package com.orangelife.service.property.impl;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.activation.DataHandler;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;

import org.apache.cxf.jaxrs.ext.multipart.Attachment;
import org.apache.geronimo.mail.util.Base64;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import com.github.pagehelper.PageHelper;
import com.orangelife.common.MybaitsGenericDaoSubLogic;
import com.orangelife.exception.AppBizException;
import com.orangelife.service.base.BaseConfig;
import com.orangelife.service.bill.IBillService;
import com.orangelife.service.bill.IComputeBillService;
import com.orangelife.service.bill.model.PreDetail;
import com.orangelife.service.paybill.IPayBillService;
import com.orangelife.service.paybill.modal.Paybill;
import com.orangelife.service.propFeeAccount.IPropFeeAccountService;
import com.orangelife.service.propFeeAccount.model.PropFeeAccountDto;
import com.orangelife.service.propFeeComm.IPropFeeCommService;
import com.orangelife.service.propFeeComm.model.PropFeeCommDto;
import com.orangelife.service.propFeeCommInfo.IPropFeeCommInfoService;
import com.orangelife.service.property.PropCollectionService;
import com.orangelife.service.property.model.CollectionItemBean;
import com.orangelife.service.property.model.FeeMerge;
import com.orangelife.service.property.model.UserBean;
import com.orangelife.service.propinfo.IPropInfoService;
import com.orangelife.service.propinfo.model.QueryModel;
import com.orangelife.setting.constant.CodeConst;
import com.orangelife.utils.DateUtil;
import com.orangelife.utils.IdUtil;
import com.orangelife.utils.MatrixToImageWriter;
import com.orangelife.utils.ProConfig;
import com.orangelife.utils.StringUtils;
import com.orangelife.webservice.service.bill.entity.BillDto;
import com.orangelife.webservice.service.property.entity.PropComputeBean;
import com.orangelife.webservice.service.property.entity.PropNoticeBean;
import com.qiniu.http.Response;
import com.qiniu.storage.UploadManager;
import com.qiniu.util.Auth;

/*****************************************************************
 * Copyright(C) ORANGELIFE Corporation CodeConst.BIS_CODE_68001015. All Rights
 * Reserved.
 *****************************************************************/
/*
 * ============================================================== 功能名
 * PropCollection类的 service层的增删改查
 * 
 * @author @author zhr
 * 
 * @version 1.1.0
 * 
 * @date 2016-01-30 12:52:51
 * ==============================================================
 */
@Service("propCollectionService")
public class PropCollectionServiceImpl extends MybaitsGenericDaoSubLogic
		implements PropCollectionService {
	private static Logger logger = Logger
			.getLogger(PropCollectionServiceImpl.class);

	@Resource(name = "cnUpperCaser")
	private CnUpperCaser cnUpperCaser;
	@Resource(name = "billService")
	private IBillService billService;
	@Resource(name = "computeBillService")
	private IComputeBillService computeBillService;
	@Resource(name = "propFeeCommInfoService")
	private IPropFeeCommInfoService propFeeCommInfoService;
	@Resource(name = "propInfoService")
	private IPropInfoService propInfoService;
	@Resource(name = "payBillService")
	private IPayBillService payBillService;
	@Resource(name = "propFeeCommService")
	private IPropFeeCommService propFeeCommService;

	@Resource(name = "propFeeAccountService")
	private IPropFeeAccountService propFeeAccountService;

	public static Logger getLogger() {
		return logger;
	}

	public static void setLogger(Logger logger) {
		PropCollectionServiceImpl.logger = logger;
	}

	@Override
	public List<Map<String, Object>> feeDetail(String isPre, String commID,
			String apartmentID, String FeeTypeID, String ts_start,
			String ts_end, String orderbytype) throws AppBizException {
		// TODO Auto-generated method stub
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("commID", commID);
		map.put("apartmentID", apartmentID);
		map.put("FeeTypeID", FeeTypeID);
		map.put("ts_start", ts_start);
		map.put("ts_end", ts_end);
		map.put("orderbytype", orderbytype);
		map.put("isPre", isPre);
		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.getCollectionItems", map);
		return list;
	}

	@Override
	public List<Map<String, Object>> FeeType(String commID, String apartmentID)
			throws AppBizException {
		// TODO Auto-generated method stub
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("commID", commID);
		map.put("apartmentID", apartmentID);

		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.getpropItems", map);
		return list;
	}

	@Override
	public List<Map<String, Object>> feecompute(PropComputeBean propComputeBean)
			throws AppBizException, ParseException {

		String commID = propComputeBean.getCommID();
		String apartmentID = propComputeBean.getApartmentID();
		String[] FeeTypeIDs = propComputeBean.getFeeTypeIDs();
		String startTime = propComputeBean.getStartTime();
		String endTime = propComputeBean.getEndTime();
		// 计算预缴金额
		List<Map<String, Object>> res = billService.creFixFeeDetail(
				apartmentID, commID, null, startTime, endTime);
		// 获取最新预缴信息

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("apartmentID", apartmentID);
		map.put("FeeTypeIDs", FeeTypeIDs);
		Map<String, Map<String, Object>> list1 = this.getSqlSession()
				.selectMap("PropFeeDetailMapper.getMinDate", map, "feeTypeID");

		for (Map<String, Object> map2 : res) {
			Map rr = list1.get(StringUtils.toString(map2.get("feeTypeID")));

			map2.put("date",
					rr == null ? "" : StringUtils.toString(rr.get("maxdate")));

		}
		// System.out.println(res);
		//
		// map.put("commID", commID);
		// // map.put("feetypeid", feetypeid);
		// map.put("FeeTypeIDs", FeeTypeIDs);
		// map.put("apartmentID", apartmentID);
		// map.put("startTime", startTime);
		// map.put("endTime", endTime);
		// map.put("round", BaseConfig.round.get(commID));
		//
		// List<Map<String, Object>> list = this.getSqlSession().selectList(
		// "CollectionItemMapper.getpropyjjs", map);

		// TODO Auto-generated method stub
		return res;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public Map<String, Object> PayBill(PropComputeBean propComputeBean,
			UserBean userbean) throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		String apartmentID = propComputeBean.getApartmentID();
		String commID = propComputeBean.getCommID();
		String endTime = propComputeBean.getEndTime();
		String startTime = propComputeBean.getStartTime();
		List<String> feeDetailIDs = propComputeBean.getFeeDetailIDs();
		String[] feeTypeIDs = propComputeBean.getFeeTypeIDs();
		String invoiceNum = propComputeBean.getInvoiceNum();
		String payType = propComputeBean.getPayType();
		String zdid = IdUtil.getuuId();// 账单id

		List<String> feeDetailIDlist = new ArrayList<String>();
		BigDecimal allamountnew = new BigDecimal("0.00" + "");
		String allamount = "0";
		// List<String> feeDetailIDlist = Arrays.asList(feeDetailIDs);
		if (feeDetailIDs.size() > 0) {
			feeDetailIDlist.addAll(feeDetailIDs);

			Map<String, Object> feeDetailIDsmap = new HashMap<String, Object>();
			feeDetailIDsmap.put("feeDetailIDs", feeDetailIDs);
			// 设置保留位数
			feeDetailIDsmap.put("round", BaseConfig.getRound(commID));
			// 修改欠缴状态改成已缴费
			int qjjfjg = this.getSqlSession().update(
					"CollectionItemMapper.updatefeedetail", feeDetailIDsmap);
			// 计算欠缴总费用
			allamount = (String) this.getSqlSession().selectOne(
					"CollectionItemMapper.getpropyqjzfy", feeDetailIDsmap);
			System.out.println("aaaaaaaaaaaaaaaaaa:" + allamount);
		}

		int i = 0;

		allamountnew = allamountnew.add(new BigDecimal(allamount));
		// Double allamount=0.0;
		if (!StringUtils.isBlank(startTime) && !StringUtils.isBlank(endTime)
				&& feeTypeIDs.length > 0) {
			while (true) {

				GregorianCalendar gc = new GregorianCalendar();
				gc.setTime(new SimpleDateFormat("yyyy-MM").parse(startTime));
				gc.add(2, i);
				String feedate = new SimpleDateFormat("yyyy-MM").format(gc
						.getTime());
				if (Integer.parseInt(feedate.replaceAll("-", "")) > Integer
						.parseInt(endTime.replaceAll("-", ""))) {
					break;
				}
				for (int j = 0; j < feeTypeIDs.length; j++) {
					Map<String, Object> map = new HashMap<String, Object>();
					map.put("commID", commID);
					map.put("feeTypeID", feeTypeIDs[j]);
					map.put("apartmentID", apartmentID);
					map.put("startTime", startTime);
					map.put("endTime", endTime);
					map.put("feedate", feedate);
					map.put("round", BaseConfig.getRound(commID));
					// 判断是否已生成改房号该类目该月份的预缴细项
					int isscyjxx = Integer.valueOf((String) this
							.getSqlSession().selectOne(
									"CollectionItemMapper.getisscyj", map));
					// 计算预缴类目金额
					String amount = this.getSqlSession().selectOne(
							"CollectionItemMapper.getpropyjj", map);
					System.out.println("bbbbbbbbbbbbbbbbbbbbbbbbbbbbb:"
							+ amount);
					// allamountnew=new BigDecimal(allamount);

					// allamount +=
					// Double.parseDouble(round(Double.parseDouble(amount),1));

					if (isscyjxx <= 0) {
						CollectionItemBean col = new CollectionItemBean();
						String feeDetailIDnew = IdUtil.getuuId();
						col.setFeeDetailID(feeDetailIDnew);
						feeDetailIDlist.add((String) feeDetailIDnew);
						col.setApartmentID(apartmentID);
						col.setFeeTypeID(feeTypeIDs[j]);

						// 保存预缴类目细项
						col.setRealamount(amount);
						col.setAmount(new BigDecimal(amount).setScale(
								Integer.parseInt(BaseConfig.getRound(commID)),
								BigDecimal.ROUND_HALF_UP).toString());

						allamountnew = allamountnew.add(new BigDecimal(col
								.getAmount()));

						col.setTimetype("0");
						col.setFeeDate(feedate);
						// col.setPayType(propComputeBean.getPayType());
						// col.setStartTime(startTime);
						// col.setEndTime(endTime);
						// col.setRemark(remark);
						// col.setDescription(description);
						col.setStatus("1");
						// col.setTscreate(tscreate);
						// col.setTsedit(tsedit);
						col.setItemStat("1");
						this.getSqlSession().insert(
								"CollectionItemMapper.insertfeedetail", col);
					}
				}
				i = i + 1;

			}
		}
		// 生成缴费账单 插入 prop_fee_paybill 表
		Map<String, Object> zdmap = new HashMap<String, Object>();
		zdmap.put("paybill_id", zdid);
		zdmap.put("apartment_id", apartmentID);
		// zdmap.put("pay_time", );

		zdmap.put("pay_money", allamountnew.toString());
		zdmap.put("invoice_num", invoiceNum);

		if (!StringUtils.isBlank(invoiceNum)) {

			zdmap.put("invoiceTime", new Date());

		}
		zdmap.put("payType", propComputeBean.getPayType());// 0:现金 1:刷卡 3:银行转账
		zdmap.put("payee_id", userbean.getUserID());// 收款人id------------------------==================
		zdmap.put("payee", userbean.getUserName());// 收款人姓名------------------------==================
		// zdmap.put("tscreate", );
		// zdmap.put("tsedit", );
		zdmap.put("item_stat", "1");

		this.getSqlSession().insert(
				"CollectionItemMapper.insertpropfeepaybill", zdmap);
		Map<String, Object> zdxxmap = new HashMap<String, Object>();
		// 插入缴费账单明细表------------------------------
		if (feeDetailIDlist.size() > 0) {

			zdxxmap.put("feeDetailIDlist", feeDetailIDlist);
			zdxxmap.put("paybill_id", zdid);
			this.getSqlSession().insert(
					"CollectionItemMapper.insertpropfeepaydetail", zdxxmap);
		}
		// 插入操作日志
		// Map<String, Object> logmap = new HashMap<String, Object>();
		// logmap.put("content", "生成缴费账单");
		// logmap.put("content_value", zdid);
		// logmap.put("operation", "1");
		// logmap.put("type", "1");
		// logmap.put("remark", "");
		//
		// insertoplog(logmap, userbean);
		return zdxxmap;
	}

	public void insertoplog(Map<String, Object> logmap, UserBean userbean)
			throws AppBizException, ParseException {
		logmap.put("log_id", IdUtil.getuuId());
		logmap.put("createuser_id", userbean.getUserID());// 操作人id--------------------------------====================
		logmap.put("item_stat", "1");
		this.getSqlSession().insert("CollectionItemMapper.insertpropfeelog",
				logmap);

	}

	public void insertpropfeepaydetail(Map<String, Object> zdxxmap)
			throws AppBizException, ParseException {
		// 插入缴费账单明细表------------------------------
		this.getSqlSession().insert(
				"CollectionItemMapper.insertpropfeepaydetail", zdxxmap);

	}

	@Override
	public List<Map<String, Object>> getpaybill(Map<String, Object> map)
			throws AppBizException, ParseException {
		int pageNo = Integer.valueOf((String) map.get("pageIndex"));
		int pageSize = Integer.valueOf((String) map.get("pageSize"));
		PageHelper.startPage(pageNo, pageSize);
		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.getjfdxx", map);
		// TODO Auto-generated method stub
		return list;
	}

	@Override
	public List<Map<String, Object>> getpaybillitem(String paybillID)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		Map<String, Object> mapa = new HashMap<String, Object>();
		List<Map<String, Object>> listnew = new ArrayList<Map<String, Object>>();
		mapa.put("paybillID", paybillID);
		List<Map<String, Object>> list1 = this.getSqlSession().selectList(
				"CollectionItemMapper.getjfdxxmx", mapa);
		// System.out.println("wwwww:"+list1.get(0).toString());
		if (list1.size() > 0) {
			Map<String, Object> mapnew = list1.get(0);
			List<Map<String, Object>> list = this.getSqlSession().selectList(
					"CollectionItemMapper.getjfdxxxxmx", paybillID);
			List<Map<String, Object>> xxlist = new ArrayList<Map<String, Object>>();
			Map<String, Object> xxmapnew = new HashMap<String, Object>();

			String feeTypeIDnew = "";
			String itemNamenew = "";
			String feedatenew = "";
			BigDecimal billMoneynew = new BigDecimal("0.0");
			String feedateold = "";
			// nn.add(nn);
			// Double billMoneynew = 0.0;
			int rqcount = 1;
			for (int i = 0; i < list.size(); i++) {

				Map<String, Object> xxmap = list.get(i);
				System.out.println("hhhhhhhhhhhhhhhhhh" + i + ":"
						+ xxmap.toString());
				if (list.size() == 1) {
					feeTypeIDnew = (String) xxmap.get("feeTypeID");
					itemNamenew = (String) xxmap.get("itemName");
					feedatenew = (String) xxmap.get("billTime");
					// billMoneynew =
					// Double.parseDouble(round(Double.parseDouble(xxmap.get("billMoney").toString()),1));
					billMoneynew = new BigDecimal(xxmap.get("billMoney")
							.toString());
					xxmapnew.put("feeTypeID", (String) xxmap.get("feeTypeID"));
					xxmapnew.put("feeTypeName", (String) xxmap.get("itemName"));
					xxmapnew.put("billTime", (String) xxmap.get("billTime"));
					xxmapnew.put("billMoney", xxmap.get("billMoney").toString());
					xxlist.add(xxmapnew);
				} else if (i == 0) {
					feeTypeIDnew = (String) xxmap.get("feeTypeID");
					itemNamenew = (String) xxmap.get("itemName");
					feedatenew = (String) xxmap.get("billTime");
					feedateold = (String) xxmap.get("billTime");
					// billMoneynew =
					// Double.parseDouble(round(Double.parseDouble(xxmap.get("billMoney").toString()),1));
					billMoneynew = new BigDecimal(xxmap.get("billMoney")
							.toString());
					// xxmapnew.put("feeTypeID", (String)
					// xxmap.get("feeTypeID"));
					// xxmapnew.put("itemName", (String) xxmap.get("itemName"));
					// xxmapnew.put("billTime", (String) xxmap.get("billTime"));
					// // xxmapnew.put("billMoney", (String)
					// xxmap.get("billMoney"));
				} else {

					System.out.println("11111111111111:" + feeTypeIDnew + "--"
							+ (String) xxmap.get("feeTypeID"));
					if (feeTypeIDnew.equals((String) xxmap.get("feeTypeID"))) {
						System.out.println("2222222222222222:" + rqcount);
						billMoneynew = billMoneynew.add(new BigDecimal(xxmap
								.get("billMoney").toString()));
						// billMoneynew +=
						// Double.parseDouble(round(Double.parseDouble(xxmap.get("billMoney").toString()),1));
						GregorianCalendar gc = new GregorianCalendar();
						gc.setTime(new SimpleDateFormat("yyyy-MM")
								.parse(feedateold));
						gc.add(2, 1);
						String feedateadd = new SimpleDateFormat("yyyy-MM")
								.format(gc.getTime());
						System.out.println("cccccccccccccccccc:" + feedateadd);
						System.out.println("cccccccccccccccccc111:"
								+ (String) xxmap.get("billTime"));
						if (feedateadd.equals((String) xxmap.get("billTime"))) {

							if (feedatenew.lastIndexOf("~") == -1) {

								feedatenew = feedatenew + "~"
										+ (String) xxmap.get("billTime");
							} else {
								feedatenew = feedatenew.substring(0,
										feedatenew.lastIndexOf("~"))
										+ "~" + (String) xxmap.get("billTime");
							}
						} else {
							feedatenew = feedatenew + ","
									+ (String) xxmap.get("billTime");
						}
						feedateold = xxmap.get("billTime").toString();
						System.out.println("ffffffffffffffddddd:" + feedatenew);
						System.out.println("ffffffffffffff:" + rqcount);
						rqcount++;
						if (i == list.size() - 1) {
							xxmapnew.put("feeTypeID", feeTypeIDnew);
							xxmapnew.put("feeTypeName", itemNamenew);
							xxmapnew.put("billTime", feedatenew);
							xxmapnew.put("billMoney", billMoneynew.toString());
							xxlist.add(xxmapnew);
						}
					} else {

						// String feeTypeIDnew="";
						// String itemNamenew="";
						// String feedatenew="";
						// Double billMoneynew=0.0;

						xxmapnew.put("feeTypeID", feeTypeIDnew);
						xxmapnew.put("feeTypeName", itemNamenew);
						xxmapnew.put("billTime", feedatenew);
						xxmapnew.put("billMoney", billMoneynew.toString());
						System.out.println("mmmmmmmmmmmmmmmmmmmm:"
								+ xxmapnew.toString());
						xxlist.add(xxmapnew);
						xxmapnew = new HashMap<String, Object>();
						feeTypeIDnew = (String) xxmap.get("feeTypeID");
						itemNamenew = (String) xxmap.get("itemName");
						feedatenew = (String) xxmap.get("billTime");
						feedateold = (String) xxmap.get("billTime");
						// billMoneynew =
						// Double.parseDouble(round(Double.parseDouble(xxmap.get("billMoney").toString()),1));
						billMoneynew = new BigDecimal(xxmap.get("billMoney")
								.toString());
						if (i == list.size() - 1) {
							xxmapnew.put("feeTypeID", feeTypeIDnew);
							xxmapnew.put("feeTypeName", itemNamenew);
							xxmapnew.put("billTime", feedatenew);
							xxmapnew.put("billMoney", billMoneynew.toString());
							xxlist.add(xxmapnew);
						}
						// feeTypeIDnew="";
						// itemNamenew="";
						// feedatenew="";
						// billMoneynew="";
						// feedatenew = (String) xxmap.get("billTime");
						rqcount = 1;
					}

				}
			}

			mapnew.put("itemList", xxlist);// list
			System.out.println("wwwww:" + mapnew.toString());// xxlist
			listnew.add(mapnew);
		}

		return listnew;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public int updatepaybill(PropComputeBean propComputeBean, UserBean userbean)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		// 修改欠缴状态改成已缴费
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("invoiceNum", propComputeBean.getInvoiceNum());
		map.put("invoiceTime", new Date());
		map.put("paybillID", propComputeBean.getPaybillID());
		int res = this.getSqlSession().update(
				"CollectionItemMapper.updateinvoiceNum", map);
		// 插入操作日志
		Map<String, Object> logmap = new HashMap<String, Object>();
		logmap.put("content", userbean.getUserName() + " 修改缴费账单发票号 "
				+ propComputeBean.getInvoiceNumBefore() + " 为： "
				+ propComputeBean.getInvoiceNum());
		logmap.put("content_value", propComputeBean.getPaybillID());
		logmap.put("operation", "2");
		logmap.put("type", "1");
		logmap.put("remark", "");

		insertoplog(logmap, userbean);
		return res;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public synchronized int deletepaybill(String paybillID, UserBean userbean)
			throws AppBizException, ParseException {

		Paybill paybill = payBillService.getPayBillDetail(paybillID);

		if (paybill == null || "0".equals(paybill.getItemStat())) {

			throw new AppBizException(CodeConst.BIS_CODE_13006, "账单已被删除");

		}
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("paybillID", paybillID);
		// TODO Auto-generated method stub
		int res = this.getSqlSession().update(
				"CollectionItemMapper.deletejfxxbefor", map);
		// int res1 = this.getSqlSession().update(
		// "CollectionItemMapper.deletejfxxend", map);
		int res2 = this.getSqlSession().update(
				"CollectionItemMapper.deletejfzd", map);
		Map<String, Object> logmap = new HashMap<String, Object>();
		logmap.put("content", "删除缴费账单");
		logmap.put("content_value", paybillID);
		logmap.put("operation", "3");
		logmap.put("type", "1");
		logmap.put("remark", "");

		insertoplog(logmap, userbean);

		payBillService.deletePayBillItem(paybillID, userbean);
		return res2;
	}

	public static String round(double v, int scale) {

		if (scale < 0) {

			throw new IllegalArgumentException(
					"The scale must be a positive integer or zero");

		}

		BigDecimal b = new BigDecimal(Double.toString(v));

		BigDecimal one = new BigDecimal("1");

		return String.valueOf(b.divide(one, scale, BigDecimal.ROUND_HALF_UP)
				.doubleValue());

	}

	private List<Map<String, Object>> dealNoticeFeedetail(
			List<Map<String, Object>> fwqflist1, Map<String, PreDetail> pre) {

		int n = fwqflist1.size();

		String tempcode = null;
		FeeMerge f = new FeeMerge();
		List<Map<String, Object>> l = new ArrayList<>();

		for (Map<String, Object> map : fwqflist1) {
			if (f.getFeeTypeID() == null) {
				f.setFeeTypeID(map.get("feeTypeID").toString());
				f.setFeeTypeName(map.get("feeTypeName").toString());
				f.dataList.add(map);

			}

			else {
				if (f.getFeeTypeID().equals(map.get("feeTypeID").toString())) {

					f.dataList.add(map);

				} else {
					PreDetail temp1 = pre != null ? pre.get(f.getFeeTypeID())
							: null;
					if (temp1 != null) {

						Map<String, Object> p = new HashMap<String, Object>();
						p.put("feeTypeID", f.getFeeTypeID());
						p.put("feeTypeName", f.getFeeTypeName());
						p.put("feePrice", temp1.getFeePrice());
						p.put("feeAmount", temp1.getFeeAmount());
						p.put("amount", temp1.getAmount());
						p.put("stardate", temp1.getStartDate());
						p.put("enddate", temp1.getEndDate());
						f.ispre = true;
						f.dataList.add(p);
					}

					l.addAll(f.mergeList2());
					f.init();
					f.setFeeTypeID(map.get("feeTypeID").toString());
					f.setFeeTypeName(map.get("feeTypeName").toString());
					f.dataList.add(map);

				}

			}

		}

		PreDetail temp1 = pre != null ? pre.get(f.getFeeTypeID()) : null;
		if (temp1 != null) {

			Map<String, Object> p = new HashMap<String, Object>();
			p.put("feeTypeID", f.getFeeTypeID());
			p.put("feeTypeName", f.getFeeTypeName());
			p.put("feePrice", temp1.getFeePrice());
			p.put("feeAmount", temp1.getFeeAmount());
			p.put("amount", temp1.getAmount());
			p.put("stardate", temp1.getStartDate());
			p.put("enddate", temp1.getEndDate());
			f.ispre = true;
			f.dataList.add(p);
		}

		l.addAll(f.mergeList2());

		System.out.println(l);

		return l;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public String procprintplan(PropNoticeBean propNoticeBean,
			UserBean userbean, String planid) throws AppBizException,
			ParseException, Exception {
		String commID = propNoticeBean.getCommID();
		String commName = propNoticeBean.getCommName();
		String buildingID = propNoticeBean.getBuildingID();
		String unitID = propNoticeBean.getUnitID();
		String[] apartmentID = propNoticeBean.getApartmentID();
		String startTime = propNoticeBean.getStartTime();
		String endTime = propNoticeBean.getEndTime();
		String limitDate = propNoticeBean.getLimitDate();
		String remark = propNoticeBean.getRemark();
		String pmName = propNoticeBean.getPmName();
		// String planid=IdUtil.getuuId();
		String buildingName = "";
		String unitName = "";
		String apartmentName = "";
		String plancontent = "";
		if (!StringUtils.isBlank(buildingID)) {// 获取楼栋名称
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("buildingID", buildingID);
			buildingName = (String) this.getSqlSession().selectOne(
					"PropNoticeMapper.getbuildingName", map);
		}
		if (!StringUtils.isBlank(unitID)) {// 获取楼栋名称
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("unitID", unitID);
			unitName = (String) this.getSqlSession().selectOne(
					"PropNoticeMapper.getunitName", map);

			if (unitName == null) {

				unitName = "";

			}
		}
		if (apartmentID != null) {
			if (apartmentID.length > 0) {// 获取房号 存在多个
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("apartmentID", apartmentID);
				apartmentName = "("
						+ (String) this.getSqlSession().selectOne(
								"PropNoticeMapper.getapartmentName", map) + ")";
			}
		}
		plancontent = buildingName + " " + unitName + " " + apartmentName + ","
				+ startTime + "~" + endTime + "," + limitDate;
		System.out.println("计划内容dd11========:" + plancontent);
		// //插入生成计划处理 prop_fee_proc_plan
		// Map<String, Object> planmap = new HashMap<String, Object>();
		// planmap.put("planID",planid);
		// planmap.put("commID", commID );
		// planmap.put("planName", plancontent);
		// planmap.put("planContent", plancontent);
		// planmap.put("planType", "2");
		// planmap.put("planStatus","2" );
		// //planmap.put("planLink", );
		// planmap.put("remark", remark);
		// // planmap.put("finishTime", );
		// // planmap.put("tscreate", );
		// // planmap.put("tsedit", );
		// planmap.put("itemTtat","1" );
		// this.getSqlSession().insert("PropNoticeMapper.insertpropfeeprocplan",
		// planmap);
		// 查询欠缴房屋信息
		Map<String, Object> fwxxmap = new HashMap<String, Object>();

		QueryModel q = new QueryModel();
		if (!StringUtils.isBlank(buildingID)) {// 获取楼栋名称
			fwxxmap.put("buildingID", buildingID);
			q.setBuildingID(buildingID);
		}
		if (!StringUtils.isBlank(unitID)) {// 获取楼栋名称
			fwxxmap.put("unitID", unitID);
			q.setUnitID(unitID);
		}
		fwxxmap.put("apartmentID", apartmentID);
		if (apartmentID.length > 0) {
			fwxxmap.put("apartmentIDstring", "11");
			q.setApartmentIDs(apartmentID);
		} else {
			fwxxmap.put("apartmentIDstring", null);
		}

		List<Map<String, Object>> fwxxlist = propInfoService
				.getApartmentInfo(q);
		System.out.println("ffffffffffff:" + fwxxlist);
		// 获取打印时间
		Map<String, Object> printtimemap = new HashMap<String, Object>();
		printtimemap.put("planid", planid);
		String printtime = (String) this.getSqlSession().selectOne(
				"PropNoticeMapper.getprinttime", printtimemap);
		// 生成打印计划excl

		PropFeeCommDto propFeeCommDto = propFeeCommService
				.findPropFeeCommByID(commID);
		// pmName = propFeeCommDto.getComm() + "物业服务收费通知单";
		String ljdz = outExcel(fwxxlist, planid, limitDate, printtime,
				commName, remark, pmName, startTime, endTime, commID,
				propFeeCommDto);
		// 修改打印计划为完成
		Map<String, Object> wcmap = new HashMap<String, Object>();
		wcmap.put("planid", planid);
		wcmap.put("plan_status", "3");
		wcmap.put("ljdz", ljdz);

		wcmap.put("finishTime", new Date());
		this.getSqlSession().update("PropNoticeMapper.updatepropfeeprocplan",
				wcmap);
		// TODO Auto-generated method stubwcmap
		return planid;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public int cutplan(String planid, String planstatus)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		Map<String, Object> wcmap = new HashMap<String, Object>();
		wcmap.put("planid", planid);
		wcmap.put("plan_status", planstatus);
		this.getSqlSession().update("PropNoticeMapper.updatepropfeeprocplan",
				wcmap);
		return 0;
	}

	public String outExcel(List<Map<String, Object>> list, String planid,
			String limitDate, String printtime, String commName, String remark,
			String pmName, String startTime, String endTime, String commID,
			PropFeeCommDto propFeeCommDto) throws AppBizException,
			ParseException, Exception, IOException {

		String sfqj = "";
		if (startTime.equals(endTime)) {
			sfqj = startTime;
		} else {
			sfqj = startTime + "~" + endTime;
		}
		HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件
		HSSFSheet sheet = wb.createSheet("收费通知单"); // --->创建了一个工作簿

		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// anchor主要用于设置图片的属性

		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张

		System.out.println(printSetup.getFitHeight() + "***************"
				+ "&&&&&&");
		// sheet.setDisplayGridlines(false);
		// sheet.setPrintGridlines(false);System.out.println("vvvvvvvvvvvvvv:"+printSetup.getFitHeight());
		sheet.setMargin(HSSFSheet.TopMargin, (double) 0.3); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.3); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.7); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin, (double) 0.5); // 右边距

		HSSFDataFormat format = wb.createDataFormat(); // --->单元格内容格式//1200
		sheet.setColumnWidth((short) 0, 20 * 233); // ---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的
		sheet.setColumnWidth((short) 1, 20 * 233); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度
		sheet.setColumnWidth((short) 2, 20 * 350);
		sheet.setColumnWidth((short) 3, 20 * 217);
		sheet.setColumnWidth((short) 4, 20 * 230);
		sheet.setDefaultRowHeight((short) 300); // ---->有得时候你想设置统一单元格的高度，就用这个方法
		sheet.protectSheet("123456");
		int sheetrow = 0;
		int numrow = 0;
		/** 所有行的样式开始 **/
		// 样式1 第一行和第二行样式
		HSSFCellStyle style = wb.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style.setBorderLeft((short) 1); // 设置左边框
		style.setBorderRight((short) 1); // 设置有边框
		style.setBorderTop((short) 1); // 设置下边框

		Font font = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setItalic(true); // --->设置是否是加粗

		font.setFontHeightInPoints((short) 13); // --->设置字体大小
		style.setFont(font);

		HSSFCellStyle style2 = wb.createCellStyle(); // 样式对象
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style2.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style2.setBorderLeft((short) 1); // 设置左边框
		style2.setBorderRight((short) 1); // 设置有边框
		style2.setBorderTop((short) 1); // 设置下边框

		Font font2 = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font2.setItalic(true); // --->设置是否是加粗
		font2.setFontHeightInPoints((short) 12); // --->设置字体大小
		style2.setFont(font2);

		// 第三行样式
		HSSFCellStyle style3 = wb.createCellStyle(); // 样式对象
		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		// style3.setBorderBottom((short)0); //设置下划线，参数是黑线的宽度
		// style3.setBorderLeft((short)0); //设置左边框
		// style3.setBorderRight((short)1); //设置有边框
		// style3.setBorderTop((short)0); //设置下边框
		Font font3 = wb.createFont();
		font3.setFontHeightInPoints((short) 5); // --->设置字体大小
		style3.setFont(font3);

		HSSFCellStyle stylecel = wb.createCellStyle(); // 样式对象
		stylecel.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel.setBorderLeft((short) 1); // 设置左边框
		stylecel.setBorderRight((short) 0); // 设置有边框
		stylecel.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel1 = wb.createCellStyle(); // 样式对象
		stylecel1.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel1.setBorderLeft((short) 0); // 设置左边框
		stylecel1.setBorderRight((short) 1); // 设置有边框
		stylecel1.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel2 = wb.createCellStyle(); // 样式对象
		// stylecel2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		stylecel2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平
		stylecel2.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel2.setBorderLeft((short) 0); // 设置左边框
		stylecel2.setBorderRight((short) 1); // 设置有边框
		stylecel2.setBorderTop((short) 0); // 设置下边框

		// 样式1 第一行和第二行样式
		HSSFCellStyle style5 = wb.createCellStyle(); // 样式对象
		style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style5.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style5.setBorderLeft((short) 1); // 设置左边框
		style5.setBorderRight((short) 1); // 设置有边框
		style5.setBorderTop((short) 1); // 设置下边框
		// Font font5 = wb.createFont();
		// font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font5.setItalic(true); //--->设置斜体
		// font5.setFontHeightInPoints((short)8); //--->设置字体大小
		// font5.set
		// style5.setFont(font5);

		HSSFCellStyle style6 = wb.createCellStyle(); // 样式对象
		style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style6.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style6.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style6.setBorderLeft((short) 1); // 设置左边框
		style6.setBorderRight((short) 1); // 设置有边框
		style6.setBorderTop((short) 1); // 设置下边框
		style6.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第11行
		HSSFCellStyle style11 = wb.createCellStyle(); // 样式对象
		style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style11.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style11.setBorderLeft((short) 1); // 设置左边框
		style11.setBorderRight((short) 1); // 设置有边框
		style11.setBorderTop((short) 1); // 设置下边框
		Font font11 = wb.createFont();
		font11.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		font11.setFontHeightInPoints((short) 8); // --->设置字体大小
		style11.setFont(font11);

		// 表格第12行
		HSSFCellStyle style12 = wb.createCellStyle(); // 样式对象
		style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style12.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style12.setBorderLeft((short) 1); // 设置左边框
		style12.setBorderRight((short) 1); // 设置有边框
		style12.setBorderTop((short) 1); // 设置下边框
		style12.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第12行
		HSSFCellStyle style13 = wb.createCellStyle(); // 样式对象
		style13.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		// style13.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style13.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style13.setBorderLeft((short) 1); // 设置左边框
		style13.setBorderRight((short) 1); // 设置有边框
		style13.setBorderTop((short) 1); // 设置下边框
		style13.setWrapText(true); // 设置是否能够换行，能够换行为true

		HSSFCellStyle style14 = wb.createCellStyle(); // 样式对象
		style14.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);// 垂直
		// style13.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style14.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style14.setBorderLeft((short) 1); // 设置左边框
		style14.setBorderRight((short) 1); // 设置有边框
		style14.setBorderTop((short) 1); // 设置下边框
		style14.setWrapText(true); // 设置是否能够换行，能够换行为true
		/** 所有行的样式结束 **/
		// //设置标题字体格式
		// // Font font = wb.createFont();
		// Font font1 = wb.createFont();
		// //设置字体样式
		// // font.setFontHeightInPoints((short)20); //--->设置字体大小
		// // font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font1.setItalic(true); //--->设置是否是加粗
		// style1.setFont(font1); //--->将字体格式加入到style1中
		// //style1.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		// //style1.setFillPattern(CellStyle.SOLID_FOREGROUND);设置单元格颜色
		// style1.setWrapText(true); //设置是否能够换行，能够换行为true
		// style1.setBorderBottom((short)1); //设置下划线，参数是黑线的宽度
		// style1.setBorderLeft((short)1); //设置左边框
		// style1.setBorderRight((short)1); //设置有边框
		// style1.setBorderTop((short)1); //设置下边框
		// style4.setDataFormat(format.getFormat("￥#,##0")); //--->设置为单元格内容为货币格式
		//
		// style5.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
		// //--->设置单元格内容为百分数格式
		int counttzdgd = 0;// 整通知单高度

		int H = 0;
		int mh = 0;
		int pageH = 0;

		// 获取当前账单生成

		Map<String, String> lastDate = propFeeCommInfoService
				.findLastDate(commID);
		String thisTime = lastDate.get("lastTime");
		Map<String, Map<String, PreDetail>> pre = null;
		// 比较打印结束时间和账单生成时间
		if (endTime.compareTo(thisTime) > 0) {

			BillDto billDto = new BillDto();
			billDto.setCommID(commID);

			billDto.setStartTime(DateUtil.addMonth(thisTime, 1));
			billDto.setEndTime(endTime);
			pre = computeBillService.commPreFeeDetail(billDto, list);
		}

		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			String fwxx = (String) map.get("building_name")
					+ StringUtils.toString(map.get("unit_name"), "")
					+ (String) map.get("apartment_num");
			String fwjzmj = StringUtils.toString(map.get("fee_area"), "");
			String owners = StringUtils.toString(map.get("ownerName"), "");
			String ownerName = "";
			if (!StringUtils.isBlank(owners)) {
				String temp[] = owners.split(",");
				ownerName = temp[0];
			}
			// 获取该房屋的欠费信息
			Map<String, Object> fwqfmap = new HashMap<String, Object>();
			String apartmentID = map.get("apartment_id").toString();
			fwqfmap.put("apartment_id", map.get("apartment_id").toString());
			fwqfmap.put("startTime", startTime);
			fwqfmap.put("endTime", endTime);
			fwqfmap.put("commID", commID);

			PropFeeAccountDto propFeeAccountDto = propFeeAccountService
					.findAccountByApartID(apartmentID);

			// List<PropFeeAccountDto>propFeeAccountDtos=

			Map<String, Object> totalMap = this.getSqlSession().selectOne(
					"PropNoticeMapper.getfhqTotal", fwqfmap);
			BigDecimal totalAmount = new BigDecimal(totalMap.get("totalAmount")
					.toString());
			if (totalAmount.compareTo(BigDecimal.ZERO) <= 0) {

				if (pre == null
						|| pre.get(map.get("apartment_id").toString()) == null) {

					continue;

				}
			}

			List<Map<String, Object>> fwqflist1 = this.getSqlSession()
					.selectList("PropNoticeMapper.getfhqfmxtzd2", fwqfmap);
			List<Map<String, Object>> fwqflist = dealNoticeFeedetail(fwqflist1,
					pre != null ? pre.get(apartmentID) : null);

			// 获取预缴的费用

			System.out.println("获取该房屋的欠费信息==============:" + fwqflist);
			// String
			counttzdgd += 1900 + fwqflist.size() * 400;

			// if(counttzdgd>18500){
			// sheet.setAutobreaks(false);
			// sheet.setRowBreak(numrow);// 設置每30行分頁打印
			// counttzdgd=1900+fwqflist.size()*400;
			// }
			int tzdgd = 0;// 通知单高度
			// 表格第一行
			numrow = sheetrow++;
			HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 28, 1000, 236,
					(short) 0, numrow, (short) 0, numrow);
			anchor1.setAnchorType(3);
			HSSFRow row0 = sheet.createRow(numrow); // --->创建一行
			HSSFCell cell0 = row0.createCell(0);
			HSSFCell cell01 = row0.createCell(1);
			HSSFCell cell02 = row0.createCell(2);
			HSSFCell cell03 = row0.createCell(3);
			HSSFCell cell04 = row0.createCell(4);
			cell01.setCellStyle(style);
			cell02.setCellStyle(style);
			cell03.setCellStyle(style);
			cell04.setCellStyle(style);

			// 四个参数分别是：起始行，起始列，结束行，结束列
			sheet.addMergedRegion(new CellRangeAddress(numrow, (short) numrow,
					0, (short) 4));
			row0.setHeightInPoints(30);
			// row0.setRowStyle(style);
			tzdgd += row0.getHeight();
			// --->创建一个单元格
			cell0.setCellStyle(style);
			// row0.getCell(1).setCellStyle(style);

			cell0.setCellValue(pmName);
			cell0.setCellStyle(style2);

			// 表格第二行
			numrow = sheetrow++;
			HSSFRow row1 = sheet.createRow(numrow);

			HSSFCell cell1 = row1.createCell(0);
			HSSFCell cell1_1 = row1.createCell(1);
			HSSFCell cell1_2 = row1.createCell(2);
			HSSFCell cell1_3 = row1.createCell(3);
			HSSFCell cell1_4 = row1.createCell(4);

			cell1_1.setCellStyle(style);
			cell1_2.setCellStyle(style);
			cell1_3.setCellStyle(style);
			cell1_4.setCellStyle(style);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 4));
			row1.setHeightInPoints(20);
			// row1.setRowStyle(style);
			tzdgd += row1.getHeight();

			cell1.setCellValue("收费通知单");
			cell1.setCellStyle(style);

			// 表格第三行
			numrow = sheetrow++;
			HSSFRow row3 = sheet.createRow(numrow);

			row3.setRowStyle(style3);
			tzdgd += row3.getHeight();
			// row3.setHeightInPoints(20);
			HSSFCell cell3 = row3.createCell(0);
			cell3.setCellValue("收费期间：" + sfqj);
			cell3.setCellStyle(stylecel);

			HSSFCell cell3_2 = row3.createCell(1);
			cell3_2.setCellValue("");
			cell3_2.setCellStyle(stylecel1);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));

			HSSFCell cell3_3 = row3.createCell(2);
			cell3_3.setCellValue("打印时间：" + printtime);
			cell3_3.setCellStyle(stylecel1);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));
			HSSFCell cell3_4 = row3.createCell(3);
			cell3_4.setCellValue("小区名称：" + commName);
			cell3_4.setCellStyle(stylecel1);

			HSSFCell cell3_5 = row3.createCell(4);
			cell3_5.setCellValue("");
			cell3_5.setCellStyle(stylecel1);

			// 表格第四行
			numrow = sheetrow++;
			HSSFRow row4 = sheet.createRow(numrow);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));

			row4.setRowStyle(style3);
			// row4.setHeightInPoints(15);
			tzdgd += row4.getHeight();
			HSSFCell cell4 = row4.createCell(0);
			cell4.setCellValue("房屋信息：" + fwxx);
			cell4.setCellStyle(stylecel);

			HSSFCell cell4_2 = row4.createCell(1);
			cell4_2.setCellValue("");
			cell4_2.setCellStyle(stylecel1);

			// row4.setRowStyle(stylecel2);
			HSSFCell cell4_3 = row4.createCell(2);
			cell4_3.setCellValue("业主名称：" + ownerName);
			cell4_3.setCellStyle(stylecel1);
			HSSFCell cell4_5 = row4.createCell(3);
			cell4_5.setCellValue("房屋计费面积：" + fwjzmj + "㎡");
			cell4_5.setCellStyle(stylecel);
			HSSFCell cell4_6 = row4.createCell(4);
			cell4_6.setCellValue("");
			cell4_6.setCellStyle(stylecel2);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));

			// 表格第五行
			numrow = sheetrow++;
			HSSFRow row5 = sheet.createRow(numrow);

			tzdgd += row5.getHeight();
			// row5.setHeightInPoints(25);

			HSSFCell cell5_0 = row5.createCell(0);
			cell5_0.setCellValue("费用名称");
			cell5_0.setCellStyle(style5);

			HSSFCell cell5_1 = row5.createCell(1);
			cell5_1.setCellValue("计费用量");
			cell5_1.setCellStyle(style5);

			HSSFCell cell5_2 = row5.createCell(2);
			cell5_2.setCellValue("单价(元)");
			cell5_2.setCellStyle(style5);

			HSSFCell cell5_3 = row5.createCell(3);
			cell5_3.setCellValue("合计应缴金额(元)");
			cell5_3.setCellStyle(style5);

			HSSFCell cell5_4 = row5.createCell(4);
			cell5_4.setCellValue("");
			cell5_4.setCellStyle(style5);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));

			// 生成快照
			Map<String, Object> kzmap = new HashMap<String, Object>();
			kzmap.put("notice_id", IdUtil.getuuId());
			kzmap.put("plan_id", planid);
			kzmap.put("apartment_id", map.get("apartment_id").toString());
			kzmap.put("fee_range", sfqj);
			kzmap.put("limit_date", limitDate);
			kzmap.put("content", fwqflist.toString());
			// kzmap.put("tscreate", );
			// kzmap.put("tsedit", );
			kzmap.put("item_stat", "1");
			this.getSqlSession().insert(
					"PropNoticeMapper.insertpropfeebillnotice", kzmap);
			BigDecimal bigsum = new BigDecimal("0.00");// 总计

			// 公摊费用
			Map<String, String> gt = new HashMap<String, String>();
			gt.put("feeTypeName", "公摊费用");
			gt.put("feeAmount", "-");
			gt.put("feePrice", "-");
			gt.put("stardate", "");
			gt.put("enddate", "");

			BigDecimal temp = BigDecimal.ZERO;

			for (int j = 0; j < fwqflist.size(); j++) {
				Map<String, Object> qjxxmap = fwqflist.get(j);
				String feeTypeName = qjxxmap.get("feeTypeName").toString();

				String amount = StringUtils.toString(qjxxmap.get("amount"));
				String oldstardate = StringUtils.toString(qjxxmap
						.get("stardate"));
				String oldenddate = StringUtils
						.toString(qjxxmap.get("enddate"));
				// if (pre != null) {
				// temp = BigDecimal.ZERO;
				//
				// if (pre.get(map.get("apartment_id").toString()) != null) {
				// PreDetail temp1 = pre.get(
				// map.get("apartment_id").toString()).get(
				// qjxxmap.get("feeTypeID").toString());
				//
				// if (temp1 != null) {
				//
				// amount = new BigDecimal(StringUtils.toString(
				// amount, "0.00")).add(temp1.getAmount())
				// .toString();
				// qjxxmap.put("feeAmount", temp1.getFeeAmount());
				// qjxxmap.put("feePrice", temp1.getFeePrice());
				// if (oldstardate == null) {
				//
				// oldstardate = DateUtil.addMonth(thisTime, 1);
				//
				// }
				// oldenddate = endTime;
				// }
				//
				// }
				//
				// }
				String feeAmount = StringUtils.toString(
						qjxxmap.get("feeAmount"), "——");
				String feePrice = StringUtils.toString(qjxxmap.get("feePrice"),
						"——");

				String type = StringUtils.toString(qjxxmap.get("type"));

				// if ("1".equals(type)) {
				// if (!StringUtils.isBlank(oldstardate)) {
				//
				// gt_amount = gt_amount.add(new BigDecimal(amount));
				//
				// if (StringUtils.isBlank(gt.get("stardate"))) {
				// gt.put("stardate", oldstardate);
				// gt.put("enddate", oldenddate);
				//
				// } else if (oldstardate.compareTo(gt.get("stardate")) < 0) {
				//
				// gt.put("stardate", oldstardate);
				// } else {
				// if (oldenddate.compareTo(gt.get("enddate")) > 0) {
				//
				// gt.put("enddate", oldenddate);
				//
				// }
				//
				// }
				//
				// }
				//
				// continue;
				// }

				// String oldamount=qjxxmap.get("oldamount").toString();
				// String sumamount=qjxxmap.get("sumamount").toString();
				if (StringUtils.isBlank(amount)) {
					// bigsum=bigsum.add(new BigDecimal("0.0"));
				} else {
					bigsum = bigsum.add(new BigDecimal(amount));
				}

				if (!StringUtils.isBlank(oldstardate)) {

					amount = String.format("%10s", amount)
							+ "   "
							+ "("
							+ (oldstardate.equals(oldenddate) ? oldstardate
									: (oldstardate + "~" + oldenddate)) + ")";

					//
					// if (oldstardate.equals(oldenddate)) {
					// amount = amount+"0   " + "(" + oldstardate + ")";
					// } else {
					// amount = amount+"0   " + "(" + oldstardate + "~"
					// + oldenddate + ")";
					// }
				} else {

					amount = "——";

				}

				System.out.println("总计==========：" + bigsum);

				// 表格第六行
				numrow = sheetrow++;
				HSSFRow row6 = sheet.createRow(numrow);
				tzdgd += row6.getHeight();
				// row6.setHeightInPoints(25);
				HSSFCell cell6_0 = row6.createCell(0);
				cell6_0.setCellValue(feeTypeName);
				cell6_0.setCellStyle(style6);

				HSSFCell cell6_1 = row6.createCell(1);
				cell6_1.setCellValue(feeAmount);
				cell6_1.setCellStyle(style6);

				HSSFCell cell6_2 = row6.createCell(2);
				cell6_2.setCellValue(feePrice);
				cell6_2.setCellStyle(style6);

				HSSFCell cell6_3 = row6.createCell(3);
				cell6_3.setCellValue(amount);
				cell6_3.setCellStyle(style6);

				HSSFCell cell6_4 = row6.createCell(4);
				cell6_4.setCellValue(amount);
				cell6_4.setCellStyle(style6);
				sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
						(short) 3, (short) 4));

			}

			// numrow = sheetrow++;
			// HSSFRow row6 = sheet.createRow(numrow);
			// tzdgd += row6.getHeight();
			// // row6.setHeightInPoints(25);
			// HSSFCell cell6_0 = row6.createCell(0);
			// cell6_0.setCellValue("公摊费用");
			// cell6_0.setCellStyle(style6);
			//
			// HSSFCell cell6_1 = row6.createCell(1);
			// cell6_1.setCellValue("——");
			// cell6_1.setCellStyle(style6);
			//
			// HSSFCell cell6_2 = row6.createCell(2);
			// cell6_2.setCellValue("——");
			// cell6_2.setCellStyle(style6);
			//
			// HSSFCell cell6_3 = row6.createCell(3);
			//
			// String amount11 = "——";
			// if (!StringUtils.isBlank(gt.get("stardate"))) {
			// bigsum = bigsum.add(new BigDecimal(gt_amount.toString()));
			//
			// amount11 = String.format("%10s", gt_amount.toString()) + "   "
			// + "(" + gt.get("stardate") + "~" + gt.get("enddate")
			// + ")";
			//
			// }
			//
			// cell6_3.setCellValue(amount11);
			// cell6_3.setCellStyle(style6);

			// 第11行

			numrow = sheetrow++;
			HSSFRow row11 = sheet.createRow(numrow);
			row11.setHeightInPoints(13);
			tzdgd += row11.getHeight();
			// row11.setRowStyle(style11);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow + 2,
					(short) 0, (short) 1));
			HSSFCell cell11_11 = row11.createCell(1);
			cell11_11.setCellStyle(style11);
			HSSFCell cell11_0 = row11.createCell(0);
			cell11_0.setCellValue("缴费期限：" + limitDate);
			cell11_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell11_1 = row11.createCell(2);
			HSSFCell cell11_3 = row11.createCell(3);
			HSSFCell cell11_4 = row11.createCell(4);
			cell11_3.setCellStyle(style11);
			cell11_4.setCellStyle(style11);
			cell11_1.setCellValue("总计金额（小写）：" + bigsum.toString());
			cell11_1.setCellStyle(style11);

			numrow = sheetrow++;
			HSSFRow row13 = sheet.createRow(numrow);
			row13.setHeightInPoints(13);

			// row13.setRowStyle(style11);
			tzdgd += row13.getHeight();
			// sheet.addMergedRegion(new CellRangeAddress(numrow-1, numrow,
			// (short) 0, (short) 1));
			// // HSSFCell cell13_0 = row11.createCell(0);
			// // cell13_0.setCellValue("缴费期限："+limitDate);
			// // cell13_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell13_0 = row13.createCell(0);
			HSSFCell cell13_1 = row13.createCell(2);
			HSSFCell cell13_2 = row13.createCell(3);
			HSSFCell cell13_4 = row13.createCell(4);
			cell13_0.setCellStyle(style11);
			cell13_2.setCellStyle(style11);
			cell13_4.setCellStyle(style11);
			cell13_1.setCellValue("总计金额（大写）："
					+ cnUpperCaser.number2CNMontrayUnit(bigsum));
			cell13_1.setCellStyle(style11);

			numrow = sheetrow++;
			HSSFRow row14_1 = sheet.createRow(numrow);
			row13.setHeightInPoints(14);

			// row13.setRowStyle(style11);
			tzdgd += row14_1.getHeight();
			// sheet.addMergedRegion(new CellRangeAddress(numrow-1, numrow,
			// (short) 0, (short) 1));
			// // HSSFCell cell13_0 = row11.createCell(0);
			// // cell13_0.setCellValue("缴费期限："+limitDate);
			// // cell13_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell14_0 = row14_1.createCell(0);
			HSSFCell cell14_1 = row14_1.createCell(2);
			HSSFCell cell14_2 = row14_1.createCell(3);
			HSSFCell cell14_4 = row14_1.createCell(4);
			cell14_0.setCellStyle(style11);
			cell14_2.setCellStyle(style11);
			cell14_4.setCellStyle(style11);

			if (propFeeAccountDto != null) {
				cell14_1.setCellValue("房屋账户余额:"
						+ (propFeeAccountDto.getMoney() != null ? propFeeAccountDto
								.getMoney() + ""
								: "0.00"));
			} else {
				cell14_1.setCellValue("房屋账户余额: 0.00");
			}
			cell14_1.setCellStyle(style11);

			// 第12行
			numrow = sheetrow++;
			HSSFRow row12 = sheet.createRow(numrow);
			int nn = 2000;
			int n = 0;
			if (!StringUtils.isBlank(remark)) {

				n = remark.split("\n").length;
			}

			if (n > 6) {

				nn = n * 350;
			}

			// row12.setRowStyle(style12);
			row12.setHeight((short) nn);

			tzdgd += row12.getHeight();

			HSSFCell cell12_0 = row12.createCell(0);

			cell12_0.setCellValue("备注：");
			cell12_0.setCellStyle(style12);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 1, (short) 3));
			HSSFCell cell12_1 = row12.createCell(1);
			HSSFCell cell12_2 = row12.createCell(2);
			HSSFCell cell12_3 = row12.createCell(3);
			HSSFCell cell12_4 = row12.createCell(4);

			int col1width = (int) Math.round((sheet.getColumnWidth(4) / 45));
			int height = nn / 20;
			// gao
			int n1 = 1400 * 255 / 1800;

			int n2 = (255 - n1) / 2;

			int wid = 75 * 1023 / col1width;
			HSSFClientAnchor anchor = new HSSFClientAnchor(120, 20, 100 + wid,
					n1 - 10, (short) 4, numrow, (short) 4, numrow);
			anchor.setAnchorType(3);

			// ByteArrayOutputStream byteArrayOut =
			// MatrixToImageWriter.getMatrix(
			// 100, 100, value);

			// 插入图片

			ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();

			byteArrayOut1.write(ProConfig.readStream(ProConfig.getImage()));
			patriarch.createPicture(anchor1,
					wb.addPicture(byteArrayOut1.toByteArray(),
							HSSFWorkbook.PICTURE_TYPE_JPEG));

			String value = ProConfig.getProValues("paybillNoticeUrl")
					+ "?commID=" + commID + "&apartmentID="
					+ map.get("apartment_id").toString();
			// ByteArrayOutputStream byteArrayOut =
			// MatrixToImageWriter.getMatrix(
			// 100, 100, value);

			// 插入图片
			if (1 == 1) {

				if (!StringUtils.isBlank(propFeeCommDto.getQrcode())) {

					ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
					byteArrayOut
							.write(Base64.decode(propFeeCommDto.getQrcode()));
					patriarch.createPicture(anchor, wb.addPicture(
							byteArrayOut.toByteArray(),
							HSSFWorkbook.PICTURE_TYPE_JPEG));
				}

			}

			cell12_1.setCellValue(remark);
			cell12_1.setCellStyle(style13);
			cell12_1.setCellStyle(style13);
			cell12_2.setCellStyle(style13);
			cell12_3.setCellStyle(style13);
			cell12_3.setCellStyle(style13);

			cell12_4.setCellStyle(style14);

			String payRemark = ProConfig.getProValues("PAYREMARK");
			cell12_4.setCellValue(!StringUtils.isBlank(propFeeCommDto
					.getQrcode()) ? payRemark : "");

			// numrow = sheetrow++;
			// HSSFRow row14 = sheet.createRow(numrow);
			// // row13.setHeightInPoints(200);
			// tzdgd += row14.getHeight();
			System.out.println("通知单打印高度:" + tzdgd);

			if (H == 0) {

				H = tzdgd;

			}

			pageH += H;

			if ((pageH + H + 200) > 18000) {

				System.out.println(pageH + "********" + numrow + "换页");
				sheet.setAutobreaks(false);
				sheet.setRowBreak(numrow);

				pageH = 0;

			} else {

				System.out.println(pageH + "********" + numrow);
				numrow = sheetrow++;
				HSSFRow row14 = sheet.createRow(numrow);
				row14.setHeight((short) 200);
				System.out.println(pageH + "********" + row14.getHeight());

				pageH = pageH + 200;

			}

			// int pagesize = 18500 / tzdgd;
			// if (i % pagesize == 0 && i != 0) {
			//
			// sheet.setAutobreaks(false);
			// sheet.setRowBreak(numrow);// 設置每30行分頁打印
			// }

		}

		ByteArrayOutputStream os = new ByteArrayOutputStream();
		wb.write(os);
		byte[] bytes = os.toByteArray();

		// 设置好账号的ACCESS_KEY和SECRET_KEY
		String ACCESS_KEY = ProConfig.getProValues("ACCESS_KEY");
		String SECRET_KEY = ProConfig.getProValues("SECRET_KEY");
		// 要上传的空间
		String bucketname = ProConfig.getProValues("bucketname");
		// 上传到七牛后保存的文件名
		String key = DateUtil.DateToString(new Date(), "yyyyMMddHHmmss")
				+ ".xls";
		// 上传文件的路径
		// 密钥配置
		Auth auth = Auth.create(ACCESS_KEY, SECRET_KEY);
		// 创建上传对象
		UploadManager uploadManager = new UploadManager();

		// 调用put方法上传
		// Response res = uploadManager.put(FilePath, null,
		// auth.uploadToken(bucketname));
		Response res = uploadManager.put(bytes, key,
				auth.uploadToken(bucketname));

		// http://7xir0t.com2.z0.glb.qiniucdn.com/9c7de7e1-3f47-4970-9c4e-470120ee2e7a.xls

		String ljdz = ProConfig.getProValues("QINIU_URL") + key;

		// FileOutputStream fileOut = null;
		// try{
		//
		// fileOut = new FileOutputStream("d:\\workbook.xls");
		// wb.write(fileOut);
		// //fileOut.close();
		// System.out.print("OK");
		// }catch(Exception e){
		// e.printStackTrace();
		// }
		// finally{
		// if(fileOut != null){
		// try {
		// fileOut.close();
		// } catch (IOException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }
		// }
		// }

		return ljdz;
	}

	@Override
	public List<Map<String, Object>> getprintplan(String commID,
			String planType, String pageIndex, String pageSize)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		// 获取该房屋的欠费信息
		PageHelper.startPage(Integer.valueOf(pageIndex),
				Integer.valueOf(pageSize));
		Map<String, Object> planmap = new HashMap<String, Object>();
		planmap.put("commID", commID);
		planmap.put("planType", planType);
		planmap.put("pageIndex", pageIndex);
		planmap.put("pageSize", pageSize);
		List<Map<String, Object>> fwqflist = this.getSqlSession().selectList(
				"PropNoticeMapper.getplans", planmap);
		return fwqflist;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public String createplan(PropNoticeBean propNoticeBean, UserBean userbean)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		String commID = propNoticeBean.getCommID();
		String commName = propNoticeBean.getCommName();
		String buildingID = propNoticeBean.getBuildingID();
		String unitID = propNoticeBean.getUnitID();
		String[] apartmentID = propNoticeBean.getApartmentID();
		String startTime = propNoticeBean.getStartTime();
		String endTime = propNoticeBean.getEndTime();
		String limitDate = propNoticeBean.getLimitDate();
		String remark = propNoticeBean.getRemark();
		String planid = IdUtil.getuuId();
		String buildingName = "";
		String unitName = "";
		String apartmentName = "";
		String plancontent = "";
		if (!StringUtils.isBlank(buildingID)) {// 获取楼栋名称
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("buildingID", buildingID);
			buildingName = (String) this.getSqlSession().selectOne(
					"PropNoticeMapper.getbuildingName", map);
		}
		if (!StringUtils.isBlank(unitID)) {// 获取楼栋名称
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("unitID", unitID);
			unitName = (String) this.getSqlSession().selectOne(
					"PropNoticeMapper.getunitName", map);
		}
		if (apartmentID != null) {
			if (apartmentID.length > 0) {// 获取房号 存在多个
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("apartmentID", apartmentID);
				apartmentName = "("
						+ (String) this.getSqlSession().selectOne(
								"PropNoticeMapper.getapartmentName", map) + ")";
			}
		}

		String str = startTime;

		if (!startTime.equals(endTime)) {
			str += "~" + endTime;
		}
		plancontent = buildingName + unitName + apartmentName + "," + str + ","
				+ limitDate;
		System.out.println("计划内容dd11========:" + plancontent);
		// 插入生成计划处理 prop_fee_proc_plan
		Map<String, Object> planmap = new HashMap<String, Object>();
		planmap.put("planID", planid);
		planmap.put("commID", commID);
		planmap.put("planName", plancontent);
		planmap.put("planContent", plancontent);
		planmap.put("planType", "2");
		planmap.put("planStatus", "2");
		// planmap.put("planLink", );
		planmap.put("remark", remark);
		// planmap.put("finishTime", );
		// planmap.put("tscreate", );
		// planmap.put("tsedit", );
		planmap.put("itemTtat", "1");
		this.getSqlSession().insert("PropNoticeMapper.insertpropfeeprocplan",
				planmap);
		return planid;
	}

	@Override
	public List<Map<String, Object>> getdic(String commID, String code)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		Map<String, Object> remarkmap = new HashMap<String, Object>();
		remarkmap.put("commID", commID);
		remarkmap.put("code", code);
		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"PropNoticeMapper.getremark", remarkmap);
		return list;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public int savedic(PropNoticeBean propNoticeBean, UserBean userbean)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		Map<String, Object> dicmap = new HashMap<String, Object>();
		dicmap.put("dic_id", IdUtil.getuuId());
		dicmap.put("code", propNoticeBean.getCode());
		dicmap.put("val", propNoticeBean.getVal());
		dicmap.put("comm_id", propNoticeBean.getCommID());
		dicmap.put("item_stat", "1");
		int i = this.getSqlSession().insert(
				"PropNoticeMapper.insertpropfeedicn", dicmap);
		return i;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public int updatedic(PropNoticeBean propNoticeBean, UserBean userbean)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		Map<String, Object> dicmap = new HashMap<String, Object>();
		dicmap.put("code", propNoticeBean.getCode());
		dicmap.put("val", propNoticeBean.getVal());
		dicmap.put("comm_id", propNoticeBean.getCommID());
		int i = this.getSqlSession().insert(
				"PropNoticeMapper.updatepropfeedic", dicmap);
		return i;
	}

	@Override
	public List<Map<String, Object>> queryshareRecord(Map<String, Object> map)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		List<Map<String, Object>> list = null;
		// if(map.get("type").toString().equals("0")){
		list = this.getSqlSession().selectList(
				"PropNoticeMapper.queryshareRecord", map);
		// }else{

		// }
		return list;
	}

	//首次公摊表读数录入
	public int saveOneshareRecord(PropNoticeBean propNoticeBean, UserBean userbean)
			throws AppBizException, ParseException {
//		logger.info("首次进修改公摊表读数");
		// TODO Auto-generated method stub
		List<Map<String, Object>> shareList = null;
		if (propNoticeBean != null) {
			shareList = propNoticeBean.getShareList();
		}
		if (shareList.size() > 0) {
			for (int i = 0; i < shareList.size(); i++) {
				Map<String, Object> sharemap = shareList.get(i);
				Map<String, Object> planmap = new HashMap<String, Object>();
				planmap.put("commID",propNoticeBean.getCommID());
				planmap.put("thisTime", sharemap.get("thisTime"));
				planmap.put("meterID", sharemap.get("meterID"));
				planmap.put("thisRecord", StringUtils.toString(
						sharemap.get("thisRecord"), null));
				planmap.put("tscreate", new Date());
				planmap.put("tsedit",new Date() );
				planmap.put("itemTtat","1" );
				this.getSqlSession().insert("PropNoticeMapper.insertpropfeetemporary",
						planmap);
			}
		}
		return 0;
	}



	//二次录入需要校验
	@Override
	public int saveshareRecord(PropNoticeBean propNoticeBean, UserBean userbean)
			throws AppBizException, ParseException {
//		logger.info("进二次录入修改公摊表读数");
		// TODO Auto-generated method stub
		List<Map<String, Object>> shareList = null;
		if (propNoticeBean != null) {
			shareList = propNoticeBean.getShareList();
		}
		if (shareList.size() > 0) {
			for (int i = 0; i < shareList.size(); i++) {
				Map<String, Object> sharemap = shareList.get(i);
				sharemap.put("period", sharemap.get("thisTime"));
				List<Map<String, Object>> list = this
						.getSqlSession()
						.selectList("PropNoticeMapper.getshareRecord", sharemap);
//          logger.info("二次录入公摊读数获取commID值:"+propNoticeBean.getCommID());

				//根据commID查询小区校验表 判断该小区是否需要校验
				Map<String,Object> map2=new HashMap<>();
				map2.put("commID",propNoticeBean.getCommID());
				map2.put("checkTemporary","1");
				map2.put("checkTemporaryType","1");//表示改小区校验状态开启
				List<Map<String, Object>> commlist = this.getSqlSession().selectList(
						"PropNoticeMapper.getpropcommchecktemporary", map2);
				logger.info("根据commID查询小区校验表长度"+commlist.size());
				if(commlist.size()>0 && commlist!=null){
					//根据相关值查找临时公摊表，如果不存在则出错
					Map<String,Object> map=new HashMap<>();
					map.put("commID",propNoticeBean.getCommID());
					map.put("meterID",sharemap.get("meterID"));
					map.put("thisTime",sharemap.get("thisTime"));
					map.put("thisRecord",StringUtils.toString(
							sharemap.get("thisRecord"), null));
					List<Map<String, Object>> templist = this.getSqlSession().selectList(
							"PropNoticeMapper.getpropfeetemporary", map);
					if(templist.size()<=0){

						throw new AppBizException(100002, "公摊表名称： '"+StringUtils.toString(
								sharemap.get("meterName"), null)+"' 录入错误," +
								"请核对上一次录入情况");
					}
				}

				if (list.size() > 0) {// 修改
					sharemap.put("thisFeeAmout", StringUtils.toString(
							sharemap.get("thisFeeAmout"), null));
					sharemap.put("thisUseAmout", StringUtils.toString(
							sharemap.get("thisUseAmout"), null));
					sharemap.put("thisRecord", StringUtils.toString(
							sharemap.get("thisRecord"), null));
					this.getSqlSession().update(
							"PropNoticeMapper.updateprop_fee_meter_record",
							sharemap);
				} else {// 新增
					sharemap.put("recordID", IdUtil.getuuId());
					sharemap.put("status", "0");
					sharemap.put("ver", "1");
					sharemap.put("itemStat", "1");
					sharemap.put("thisFeeAmout", StringUtils.toString(
							sharemap.get("thisFeeAmout"), null));
					sharemap.put("thisUseAmout", StringUtils.toString(
							sharemap.get("thisUseAmout"), null));
					sharemap.put("thisRecord", StringUtils.toString(
							sharemap.get("thisRecord"), null));
					this.getSqlSession().insert(
							"PropNoticeMapper.insertprop_fee_meter_record2",
							sharemap);
				}
			}
		}
		return 0;
	}



	@Override
	public List<Map<String, Object>> queryapartmentRecord(
			Map<String, Object> map, String pageIndex, String pageSize)
			throws AppBizException, ParseException {
		PageHelper.startPage(Integer.valueOf(pageIndex),
				Integer.valueOf(pageSize));
		List<Map<String, Object>> list = null;
		list = this.getSqlSession().selectList(
				"PropNoticeMapper.queryapartmentRecord", map);
		return list;
	}

	@Override
	public Map<String, Object> queryapartmentRecordsum(Map<String, Object> map)
			throws AppBizException, ParseException {
		Map<String, Object> mapsum = this.getSqlSession().selectOne(
				"PropNoticeMapper.queryapartmentRecordsum", map);
		return mapsum;
	}

	@Override
	public List<Map<String, Object>> downmodle(Map<String, Object> map)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		List<Map<String, Object>> list = null;
		list = this.getSqlSession()
				.selectList("PropNoticeMapper.getmodle", map);
		return list;
	}

	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public Map<String, Object> uploadFileList(List<Attachment> attachments,
			HttpServletRequest request, UserBean userbean)
			throws AppBizException, ParseException, IOException,
			InvalidFormatException {
		// TODO Auto-generated method stub
		String ishf = "";
		String isnull = "";

		//
		Map<String, Object> resmap = new HashMap<String, Object>();
		if (attachments.size() > 0)
			System.out.println("ok");

		for (Attachment attach : attachments) {
			DataHandler dh = attach.getDataHandler();
			System.out.println(attach.getContentType().toString());
			InputStream inp = dh.getInputStream();
			// Workbook wb = null;
			// wb = WorkbookFactory.create(inp);
			ExcelReader ex = new ExcelReader(inp);
			List<Map<String, Object>> shareList = new ArrayList<Map<String, Object>>();
			;

			List<String[]> ss = ex.getAllData(0);
			if (ss.size() > 2) {

				String[] xxstr1 = ss.get(0);
				String commID = xxstr1[0];
				Map<String, String> lastDate = propFeeCommInfoService
						.findLastDate(commID);
				String thisTime = lastDate.get("nextTime");

				for (int i = 2; i < ss.size(); i++) {
					Map<String, Object> zhmap = new HashMap<String, Object>();
					String[] xxstr = ss.get(i);
					zhmap.put("meterID", xxstr[0]);

					Map<String, Object> lastmap = (Map<String, Object>) this
							.getSqlSession().selectOne(
									"PropNoticeMapper.getperiod", zhmap);
					String lastperiod = null;
					String lastrecord = null;

					if (lastmap != null) {

						lastperiod = lastmap.get("period").toString();
						lastrecord = lastmap.get("record").toString();

					}

					String thisperiod = thisTime;
					if (StringUtils.isBlank(lastperiod)) {

						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
						thisperiod = sdf.format(new Date());
						continue;
					} else {
						GregorianCalendar gc = new GregorianCalendar();
						gc.setTime(new SimpleDateFormat("yyyy-MM")
								.parse(lastperiod));
						gc.add(2, 1);
						thisperiod = new SimpleDateFormat("yyyy-MM").format(gc
								.getTime());
					}
					zhmap.put("thisTime", thisTime);
					zhmap.put("period", thisTime);
					if (isNumeric(xxstr[3])) {
						if (StringUtils.isBlank(xxstr[3])) {
							isnull += xxstr[1] + ",";
						}
						zhmap.put("thisRecord", xxstr[3]);

						if (!StringUtils.isBlank(lastrecord)) {
							if (StringUtils.isBlank(xxstr[3])) {

								zhmap.put("thisUseAmout", null);
								shareList.add(zhmap);
							} else {

								BigDecimal m = new BigDecimal(xxstr[3])
										.subtract(new BigDecimal(lastrecord));

								if (m.compareTo(BigDecimal.ZERO) >= 0) {

									zhmap.put("thisUseAmout", m.toString());
									shareList.add(zhmap);

								} else {

									ishf += xxstr[1] + ",";
								}

							}

						} else {
							zhmap.put("thisUseAmout", xxstr[3]);
						}
					} else {
						ishf += xxstr[1] + ",";
					}

				}
			}
			System.out.println("ggggggggggggggggggg:" + shareList.toString());
			if (shareList.size() > 0) {
				for (int i = 0; i < shareList.size(); i++) {
					Map<String, Object> sharemap = shareList.get(i);
					List<Map<String, Object>> list = this.getSqlSession()
							.selectList("PropNoticeMapper.getshareRecord",
									sharemap);
					if (list.size() > 0) {// 修改
						this.getSqlSession().update(
								"PropNoticeMapper.updateprop_fee_meter_record",
								sharemap);
					} else {// 新增
						sharemap.put("recordID", IdUtil.getuuId());
						sharemap.put("status", "0");
						sharemap.put("ver", "1");
						sharemap.put("itemStat", "1");
						this.getSqlSession()
								.insert("PropNoticeMapper.insertprop_fee_meter_record2",
										sharemap);
					}
				}
			}
		}
		String resstr = "";
		if (!"".equals(ishf)) {
			resstr += ishf + "存在不合法数据（必须是数字,必须大于上期读数），请检查修改后重新导入或者手工录入修改。";

		}

		if (!"".equals(isnull)) {

			resstr += isnull + "存在空值，请检查修改后重新导入或者手工录入修改。";
		}
		if ("".equals(resstr)) {
			resmap.put("code", "0");
			resmap.put("note", "导入成功");
		} else {
			resmap.put("code", "99");
			resmap.put("note", resstr);
		}
		return resmap;
	}

	public static boolean isNumeric(String str) {
		for (int i = str.length(); --i >= 0;) {
			if (!Character.isDigit(str.charAt(i))) {
				return false;
			}
		}
		return true;
	}

	@Override
	public List<Map<String, Object>> querymetertype(String commID)
			throws AppBizException, ParseException {
		// TODO Auto-generated method stub
		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"PropNoticeMapper.getprop_fee_meter_type", commID);
		return list;
	}

	@Override
	public Map<String, Object> getpaybilltj(Map<String, Object> map)
			throws AppBizException, ParseException {

		Map<String, Object> list = this.getSqlSession().selectOne(
				"CollectionItemMapper.getjfdxxTj", map);
		// TODO Auto-generated method stub
		return list;
	}

	@SuppressWarnings("unchecked")
	@Override
	@Transactional(rollbackFor = { AppBizException.class, Exception.class,
			RuntimeException.class })
	public Map<String, Object> PayBillNew(PropComputeBean propComputeBean,
			UserBean userbean) throws Exception {
		String apartmentID = propComputeBean.getApartmentID();
		String commID = propComputeBean.getCommID();
		String endTime = propComputeBean.getEndTime();
		String startTime = propComputeBean.getStartTime();
		List<String> feeDetailIDlist = propComputeBean.getFeeDetailIDs();
		String[] feeTypeIDs = propComputeBean.getFeeTypeIDs();
		String invoiceNum = propComputeBean.getInvoiceNum();
		String zdid = IdUtil.getuuId();// 账单id

		BigDecimal allamountnew = new BigDecimal("0.00" + "");
		String allamount = "0";
		String maxDate = "";
		String minDate = "";
		Map<String, Object> temap = new HashMap<String, Object>();
		// List<String> feeDetailIDlist = Arrays.asList(feeDetailIDs);
		if (feeDetailIDlist.size() > 0) {

			Map<String, Object> feeDetailIDsmap = new HashMap<String, Object>();
			feeDetailIDsmap.put("feeDetailIDs", feeDetailIDlist);
			// 设置保留位数
			feeDetailIDsmap.put("round", BaseConfig.getRound(commID));
			// 修改欠缴状态改成已缴费
			/*
			 * int qjjfjg = this.getSqlSession().update(
			 * "CollectionItemMapper.updatefeedetail", feeDetailIDsmap);
			 */
			// 计算欠缴总费用
			temap = this.getSqlSession().selectOne(
					"CollectionItemMapper.getpropyqjzfyAndRange",
					feeDetailIDsmap);
			allamount = temap.get("sm").toString();
			maxDate = temap.get("mx").toString();
			minDate = temap.get("mn").toString();

		}

		int i = 0;

		allamountnew = allamountnew.add(new BigDecimal(allamount));

		List<Map<String, Object>> ram = null;
		// 计算预缴的数据

		if (propComputeBean.getPreFeeList() != null
				&& propComputeBean.getPreFeeList().size() > 0) {
			List<Map<String, Object>> ll = computeBillService.getPreFeeDetail(
					propComputeBean.getCommID(), apartmentID,
					propComputeBean.getPreFeeList());

			if (ll == null || ll.size() == 0) {

				throw new AppBizException(100001, "计算预缴数据异常，请检查计费规则");

			}

			for (Map<String, Object> map : ll) {

				String amount = StringUtils.toString(map.get("amount"));

				CollectionItemBean col = new CollectionItemBean();
				String feeDetailIDnew = IdUtil.getuuId();
				col.setFeeDetailID(feeDetailIDnew);
				col.setRuleID(StringUtils.toString(map.get("ruleID")));
				col.setApartmentID(apartmentID);
				col.setFeeTypeID(StringUtils.toString(map.get("feeTypeID")));
				col.setFeeDate(StringUtils.toString(map.get("feeDate")));
				col.setType("2");
				col.setIspre("2");
				// 保存预缴类目细项
				col.setRealamount(amount);
				col.setAmount(amount);
				col.setFeeAmount(StringUtils.toString(map.get("feeAmount")));
				col.setFeePrice(StringUtils.toString(map.get("feePrice")));
				allamountnew = allamountnew
						.add(new BigDecimal(col.getAmount()));

				col.setTimetype("0");
				col.setStatus("0");
				col.setItemStat("1");

				List<CollectionItemBean> colllist = this.getSqlSession()
						.selectList(
								"CollectionItemMapper.selectFeedetailByBean",
								col);
				if (colllist != null && colllist.size() > 0) {

					if ("1".equals(colllist.get(0).getStatus())) {

						throw new AppBizException(100001, "预缴信息错误，存在已预缴信息");

					}
					feeDetailIDlist.add(colllist.get(0).getFeeDetailID());
					continue;
				} else {
					feeDetailIDlist.add(col.getFeeDetailID());
					this.getSqlSession().insert(
							"CollectionItemMapper.insertfeedetail", col);
				}

			}
		}

		// 生成缴费账单 插入 prop_fee_paybill 表
		Map<String, Object> zdmap = new HashMap<String, Object>();
		zdmap.put("paybill_id", zdid);
		zdmap.put("apartment_id", apartmentID);
		zdmap.put("type", "2");
		zdmap.put("pay_money", allamountnew.toString());
		zdmap.put("invoice_num", invoiceNum);
		// 设置缴费状态
		zdmap.put("status", "0");
		zdmap.put("monthrange", minDate + "至" + maxDate);
		System.out.println(minDate + "+" + maxDate);

		String paybillexpiretime = ProConfig.getProValues("paybillexpiretime");

		int etime = Integer.parseInt(paybillexpiretime);
		// 设置有效时间
		Date now = new Date();
		Date time = DateUtil.addMinute(now, etime);
		// Date time = new Date(new Date().getTime() + 3 * 24 * 60 * 60 * 1000);
		SimpleDateFormat formatea = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		// 设置过期时间
		String expire_time = formatea.format(time);

		zdmap.put("expire_time", expire_time);
		zdmap.put("type", propComputeBean.getType());

		if (!StringUtils.isBlank(invoiceNum)) {

			zdmap.put("invoiceTime", new Date());

		}
		zdmap.put("payType", propComputeBean.getPayType());// 0:现金 1:刷卡 3:银行转账
		zdmap.put("payee_id", userbean.getUserID());//
		// 收款人id------------------------==================
		zdmap.put("payee", userbean.getUserName());//
		// 收款人姓名------------------------==================
		zdmap.put("item_stat", "1");

		this.getSqlSession().insert(
				"CollectionItemMapper.insertpropfeepaybill", zdmap);
		Map<String, Object> zdxxmap = new HashMap<String, Object>();
		// 插入缴费账单明细表------------------------------
		if (feeDetailIDlist.size() > 0) {

			zdxxmap.put("feeDetailIDlist", feeDetailIDlist);
			zdxxmap.put("paybill_id", zdid);
			this.getSqlSession().insert(
					"CollectionItemMapper.insertpropfeepaydetail", zdxxmap);
		}
		// 插入操作日志
		// Map<String, Object> logmap = new HashMap<String, Object>();
		// logmap.put("content", "生成缴费账单");
		// logmap.put("content_value", zdid);
		// logmap.put("operation", "1");
		// logmap.put("type", "1");
		// logmap.put("remark", "");
		// insertoplog(logmap, userbean);

		// 生成二维码
		String value = ProConfig.getProValues("paybillQRcodeUrl") + "?commID="
				+ commID + "&paybillID=" + zdid;
		String QRcode = MatrixToImageWriter.getBase64BitMatrix(300, 300, value);

		Map<String, Object> map = new HashMap<String, Object>();

		map.put("zdid", zdid);
		map.put("QRcode", QRcode);

		return map;
	}

	@Override
	public void confirmCollection(String paybillId) {
		Map<String, Object> map = new HashMap<String, Object>();

		map.put("paybillID", paybillId);
		map.put("status", "2");

		Map<String, Object> map1 = new HashMap<String, Object>();

		map1.put("paybillID", paybillId);
		map1.put("status", "1");

		this.getSqlSession().update("CollectionItemMapper.updatePaybillStatus",
				map);
		this.getSqlSession().update(
				"CollectionItemMapper.updatePaydetailStatus", map1);

	}

	@Override
	public List<Map<String, Object>> getCollectionInfoByUserId(String userId) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("userId", userId);

		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.selectInfoByUserId", map);

		return list;
	}

	@Override
	public List<Map<String, Object>> getPayBillInfoGroupForApp(
			String apartmentid,String isPre) {
		logger.info("进2222");
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("apartmentid", apartmentid);
		map.put("isPre", isPre);

		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.getPayBillInfoGroupForApp", map);

		return list;
	}

	@Override
	public List<Map<String, Object>> getHistoryPayBillInfoForApp(
			String paybillId) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("paybillId", paybillId);

		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.getHistoryPayBillInfoForApp", map);

		return list;
	}

	@Override
	public List<Map<String, Object>> getHistoryInfoListForApp(String userId,
			String apartmentId, String paybillId) {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("apartmentId", apartmentId);
		map.put("paybillId", paybillId);
		map.put("memId", userId);
		List<Map<String, Object>> list = this.getSqlSession().selectList(
				"CollectionItemMapper.getHistoryInfoListForApp", map);

		return list;
	}

	@Override
	public List<Map<String, Object>> queryshareRecords(Map<String, Object> map)
			throws AppBizException {
		// TODO Auto-generated method stub
		List<Map<String, Object>> list = null;
		// if(map.get("type").toString().equals("0")){
		list = this.getSqlSession().selectList(
				"PropNoticeMapper.queryshareRecords", map);
		// }else{

		// }
		return list;
	}

	@Override
	public List<Map<String, Object>> queryapartmentRecords(
			Map<String, Object> map, String pageIndex, String pageSize)
			throws AppBizException, ParseException {
		PageHelper.startPage(Integer.valueOf(pageIndex),
				Integer.valueOf(pageSize));
		List<Map<String, Object>> list = null;
		list = this.getSqlSession().selectList(
				"PropNoticeMapper.queryapartmentRecords", map);
		return list;
	}

	@Override
	public Map<String, Object> queryapartmentRecordsums(Map<String, Object> map)
			throws AppBizException {
		Map<String, Object> mapsum = this.getSqlSession().selectOne(
				"PropNoticeMapper.queryapartmentRecordsums", map);
		return mapsum;
	}

	private List<Map<String, Object>> dealNoticeFeedetailNew(
			List<Map<String, Object>> fwqflist1, Map<String, PreDetail> pre,
			Map<String, Map<String, Object>> rules, Map<String, String> param) {

		// 获取该房屋属计费的规则

		Map<String, Map<String, Object>> rule1 = this
				.getSqlSession()
				.selectMap("computeRuleMapper.getDsRules", param, "fee_type_id");

		int n = fwqflist1.size();

		String tempcode = null;
		FeeMerge f = new FeeMerge();

		List<FeeMerge> ls = new ArrayList<FeeMerge>();
		List<Map<String, Object>> l = new ArrayList<>();
		Map<String, Object> rule = null;
		Map<String, Object> oldRule = null;
		String ruleID = null;
		for (Map<String, Object> map : fwqflist1) {
			if (f.getFeeTypeID() == null) {
				rule = rule1.get(map.get("feeTypeID").toString());
				if (rule != null) {

					ruleID = rule.get("rule_id").toString();
					oldRule = rules.get(ruleID);
					if (oldRule == null) {
						Map<String, Object> j = JSON.parseObject(
								StringUtils.toString((rule.get("param_val"))),
								Map.class);
						if (rule.get("param_val") == null) {
							rule.put("type", "2");
						} else {

							rule.put("type", "3");
							TypeReference<List<Map<String, String>>> t = new TypeReference<List<Map<String, String>>>() {
							};
							List<Map<String, String>> rs = JSON.parseObject(j
									.get("ratios").toString(), t);
							StringBuffer b = new StringBuffer();
							for (Map<String, String> map2 : rs) {
								b.append(map2.get("start")).append("-")
										.append(map2.get("end")).append(":")
										.append(map2.get("ratio")).append("元;");
							}
							rule.put("price", b.toString());
						}
						rules.put(ruleID, rule);
						oldRule = rule;
					}
					if ("3".equals(oldRule.get("type").toString())) {
						f.price = oldRule.get("price").toString();
						f.type = 3;
					} else {
						f.type = 2;
					}
				}

				f.setFeeTypeID(map.get("feeTypeID").toString());
				f.setFeeTypeName(map.get("s_fee_type_name").toString());
				f.setScode(map.get("s_code").toString());

				f.dataList.add(map);

			}

			else {
				if (f.getFeeTypeID().equals(map.get("feeTypeID").toString())) {
					f.dataList.add(map);
				} else {
					PreDetail temp1 = pre != null ? pre.get(f.getFeeTypeID())
							: null;
					if (temp1 != null) {
						Map<String, Object> p = new HashMap<String, Object>();
						p.put("feeTypeID", f.getFeeTypeID());
						p.put("feeTypeName", f.getFeeTypeName());
						p.put("feePrice", temp1.getFeePrice());
						p.put("feeAmount", temp1.getFeeAmount());
						p.put("amount", temp1.getAmount());
						p.put("stardate", temp1.getStartDate());
						p.put("enddate", temp1.getEndDate());
						f.ispre = true;
						f.dataList.add(p);
					}
					f.merge();
					ls.add(f);
					f = new FeeMerge();
					f.init();

					rule = rule1.get(map.get("feeTypeID").toString());
					if (rule != null) {

						ruleID = rule.get("rule_id").toString();
						oldRule = rules.get(ruleID);
						if (oldRule == null) {
							Map<String, Object> j = JSON
									.parseObject(StringUtils.toString((rule
											.get("param_val"))), Map.class);
							if (rule.get("param_val") == null) {
								rule.put("type", "2");
							} else {
								rule.put("type", "3");
								TypeReference<List<Map<String, String>>> t = new TypeReference<List<Map<String, String>>>() {
								};
								List<Map<String, String>> rs = JSON
										.parseObject(
												j.get("ratios").toString(), t);
								StringBuffer b = new StringBuffer();
								for (Map<String, String> map2 : rs) {
									b.append(map2.get("start")).append("-")
											.append(map2.get("end"))
											.append(":")
											.append(map2.get("ratio"))
											.append("元;");
								}
								rule.put("price", b.toString());
							}
							rules.put(ruleID, rule);
							oldRule = rule;
						}
						if ("3".equals(oldRule.get("type").toString())) {
							f.price = oldRule.get("price").toString();
							f.type = 3;
						} else {
							f.type = 2;
						}
					}

					f.setFeeTypeID(map.get("feeTypeID").toString());
					f.setFeeTypeName(map.get("s_fee_type_name").toString());
					f.setScode(map.get("s_code").toString());
					f.dataList.add(map);
				}
			}
		}
		PreDetail temp1 = pre != null ? pre.get(f.getFeeTypeID()) : null;
		if (temp1 != null) {
			Map<String, Object> p = new HashMap<String, Object>();
			p.put("feeTypeID", f.getFeeTypeID());
			p.put("feeTypeName", f.getFeeTypeName());
			p.put("feePrice", temp1.getFeePrice());
			p.put("feeAmount", temp1.getFeeAmount());
			p.put("amount", temp1.getAmount());
			p.put("stardate", temp1.getStartDate());
			p.put("enddate", temp1.getEndDate());

			f.ispre = true;
			f.dataList.add(p);
		}
		f.merge();
		// l.addAll(f.mergeList2());
		ls.add(f);

		// 数据合并
		Map<String, Map<String, Object>> m = new HashMap<>();

		Map<String, Object> mm = null;

		for (FeeMerge f1 : ls) {
			if (f1.isOne()) {

				l.addAll(f1.dataLists);

			} else {
				mm = m.get(f1.getScode());
				if (mm == null) {
					mm = new HashMap<String, Object>();

					mm = f1.dataLists.get(0);
					m.put(f1.getScode(), mm);
					l.add(mm);
					System.out.println(StringUtils.toString(f1.dataLists.get(0)
							.get("amount"), "0")
							+ "***********");
				} else {

					mm.put("amount",
							new BigDecimal(StringUtils.toString(
									mm.get("amount"), "0")).add(new BigDecimal(
									StringUtils.toString(f1.dataLists.get(0)
											.get("amount"), "0"))));

					mm.put("enddate", StringUtils.bigger(mm.get("enddate"),
							f1.dataLists.get(0).get("enddate")));
					mm.put("stardate", StringUtils.smaller(mm.get("stardate"),
							f1.dataLists.get(0).get("stardate")));

				}

			}

		}

		System.out.println(l + "*****88");
		return l;
	}

	private List<Map<String, Object>> dealNoticeFeedetailNew2(
			List<Map<String, Object>> fwqflist1, Map<String, PreDetail> pre,
			Map<String, Map<String, Object>> rules, Map<String, String> param) {

		// 获取该房屋属计费的规则

		Map<String, Map<String, Object>> rule1 = this.getSqlSession()
				.selectMap("computeRuleMapper.getDsRules2", param,
						"fee_type_id");

		int n = fwqflist1.size();

		String tempcode = null;
		FeeMerge f = new FeeMerge();

		List<FeeMerge> ls = new ArrayList<FeeMerge>();
		List<Map<String, Object>> l = new ArrayList<>();
		Map<String, Object> rule = null;
		Map<String, Object> oldRule = null;
		String ruleID = null;
		for (Map<String, Object> map : fwqflist1) {
			if (f.getFeeTypeID() == null) {
				rule = rule1.get(map.get("feeTypeID").toString());
				if (rule != null) {

					ruleID = rule.get("rule_id").toString();
					oldRule = rules.get(ruleID);
					if (oldRule == null) {
						if ("0".equals(rule.get("type").toString())) {
							rule.put("price", rule.get("rule").toString()
									.split(";")[0]);
						} else if ("1".equals(rule.get("type").toString())) {
							if (rule.get("rule").toString().split(";").length > 2) {
								rule.put("price", rule.get("rule").toString()
										.split(";")[2]);
							}
						} else {

							Map<String, Object> j = JSON
									.parseObject(StringUtils.toString((rule
											.get("param_val"))), Map.class);
							TypeReference<List<Map<String, String>>> t = new TypeReference<List<Map<String, String>>>() {
							};
							List<Map<String, String>> rs = JSON.parseObject(j
									.get("ratios").toString(), t);
							StringBuffer b = new StringBuffer();
							for (Map<String, String> map2 : rs) {
								b.append(map2.get("start")).append("-")
										.append(map2.get("end")).append(":")
										.append(map2.get("ratio")).append("元;");
							}
							rule.put("price", b.toString());

						}
						rules.put(ruleID, rule);
						oldRule = rule;
					}

					if (!oldRule.get("type").equals("0")) {

						if (oldRule.get("feeType").equals("0")) {
							f.feeAmount = param.get("feeArea");
							f.type = 1;

						} else {

							f.type = 2;
						}
					}
					f.price = StringUtils.toString(oldRule.get("price")
							.toString());
				}
				f.setFeeTypeID(map.get("feeTypeID").toString());
				f.setFeeTypeName(map.get("s_fee_type_name").toString());
				f.setScode(map.get("s_code").toString());
				f.dataList.add(map);

			}

			else {
				if (f.getFeeTypeID().equals(map.get("feeTypeID").toString())) {
					f.dataList.add(map);
				} else {
					PreDetail temp1 = pre != null ? pre.get(f.getFeeTypeID())
							: null;
					if (temp1 != null) {
						Map<String, Object> p = new HashMap<String, Object>();
						p.put("feeTypeID", f.getFeeTypeID());
						p.put("feeTypeName", f.getFeeTypeName());
						p.put("feePrice", temp1.getFeePrice());
						p.put("feeAmount", temp1.getFeeAmount());
						p.put("amount", temp1.getAmount());
						p.put("stardate", temp1.getStartDate());
						p.put("enddate", temp1.getEndDate());
						f.ispre = true;
						f.dataList.add(p);
					}
					f.merge2();
					ls.add(f);
					f = new FeeMerge();
					f.init();

					rule = rule1.get(map.get("feeTypeID").toString());
					if (rule != null) {

						ruleID = rule.get("rule_id").toString();
						oldRule = rules.get(ruleID);
						if (oldRule == null) {

							if ("0".equals(rule.get("type").toString())) {

								rule.put("price", rule.get("rule").toString()
										.split(";")[0]);
							} else if ("1".equals(rule.get("type").toString())) {
								if (rule.get("rule").toString().split(";").length > 2) {
									rule.put("price", rule.get("rule")
											.toString().split(";")[2]);
								}
							} else {

								Map<String, Object> j = JSON.parseObject(
										StringUtils.toString((rule
												.get("param_val"))), Map.class);

								TypeReference<List<Map<String, String>>> t = new TypeReference<List<Map<String, String>>>() {
								};
								List<Map<String, String>> rs = JSON
										.parseObject(
												j.get("ratios").toString(), t);
								StringBuffer b = new StringBuffer();
								for (Map<String, String> map2 : rs) {
									b.append(map2.get("start")).append("-")
											.append(map2.get("end"))
											.append(":")
											.append(map2.get("ratio"))
											.append("元;");
								}
								rule.put("price", b.toString());

							}

							rules.put(ruleID, rule);
							oldRule = rule;
						}

						if (!oldRule.get("type").equals("0")) {

							if (oldRule.get("feeType").equals("0")) {

								f.type = 1;
								f.feeAmount = param.get("feeArea");

							} else {

								f.type = 2;
							}

						}
						f.price = StringUtils.toString(oldRule.get("price")
								.toString());
					}

					f.setFeeTypeID(map.get("feeTypeID").toString());
					f.setFeeTypeName(map.get("s_fee_type_name").toString());
					f.setScode(map.get("s_code").toString());
					f.dataList.add(map);
				}
			}
		}
		PreDetail temp1 = pre != null ? pre.get(f.getFeeTypeID()) : null;
		if (temp1 != null) {
			Map<String, Object> p = new HashMap<String, Object>();
			p.put("feeTypeID", f.getFeeTypeID());
			p.put("feeTypeName", f.getFeeTypeName());
			p.put("feePrice", temp1.getFeePrice());
			p.put("feeAmount", temp1.getFeeAmount());
			p.put("amount", temp1.getAmount());
			p.put("stardate", temp1.getStartDate());
			p.put("enddate", temp1.getEndDate());

			f.ispre = true;
			f.dataList.add(p);
		}
		f.merge2();
		ls.add(f);

		// 数据合并
		Map<String, Map<String, Object>> m = new HashMap<>();
		Map<String, Object> mm11 = null;
		Map<String, Object> mm = null;
		// 记录每个二级类目的数量
		Map<String, Integer> mm1 = new HashMap<>();

		// 生成List
		for (FeeMerge f1 : ls) {
			mm11 = f1.dataLists.get(0);
			if (mm1.get(f1.getScode()) == null) {

				if (new BigDecimal(
						StringUtils.toString(mm11.get("amount"), "0"))
						.compareTo(BigDecimal.ZERO) > 0) {

					mm1.put(f1.getScode(), 1);

				} else {
					mm1.put(f1.getScode(), 0);

				}

			} else {
				if (new BigDecimal(
						StringUtils.toString(mm11.get("amount"), "0"))
						.compareTo(BigDecimal.ZERO) > 0) {

					mm1.put(f1.getScode(), mm1.get(f1.getScode()) + 1);

				} else {
					mm1.put(f1.getScode(), mm1.get(f1.getScode()) + 0);

				}

			}

			if (f1.isOne()) {

				l.addAll(f1.dataLists);

			} else {
				mm = m.get(f1.getScode());

				if (mm == null) {

					mm = f1.dataLists.get(0);
					m.put(f1.getScode(), mm);
					l.add(mm);
				} else {
					mm.put("amount",
							new BigDecimal(StringUtils.toString(
									mm.get("amount"), "0")).add(new BigDecimal(
									StringUtils.toString(f1.dataLists.get(0)
											.get("amount"), "0"))));

					mm.put("enddate", StringUtils.bigger(mm.get("enddate"),
							f1.dataLists.get(0).get("enddate")));
					mm.put("stardate", StringUtils.smaller(mm.get("stardate"),
							f1.dataLists.get(0).get("stardate")));

				}

			}

		}

		List<Map<String, Object>> l1 = new ArrayList<>();

		// 如果二级类目数量大于1为空的值不加入列表

		for (Map<String, Object> map : l) {

			if (new BigDecimal(StringUtils.toString(map.get("amount"), "0"))
					.compareTo(BigDecimal.ZERO) > 0) {

				l1.add(map);
			} else {
				if (mm1.get(map.get("s_code")) == 0) {
					mm1.put(map.get("s_code").toString(), 1);
					l1.add(map);

				}

			}

		}
		// System.out.println(l+"*****88");
		return l1;
	}

	@Override
	public String procprintplanNew(PropNoticeBean propNoticeBean,
			UserBean userbean, String planid,String pmID) throws AppBizException,
			ParseException, Exception, IOException {
		String commID = propNoticeBean.getCommID();
		String commName = propNoticeBean.getCommName();
		String buildingID = propNoticeBean.getBuildingID();
		String unitID = propNoticeBean.getUnitID();
		String[] apartmentID = propNoticeBean.getApartmentID();
		String startTime = propNoticeBean.getStartTime();
		String endTime = propNoticeBean.getEndTime();
		String limitDate = propNoticeBean.getLimitDate();
		String remark = propNoticeBean.getRemark();
		String pmName = propNoticeBean.getPmName();
		// String planid=IdUtil.getuuId();
		String buildingName = "";
		String unitName = "";
		String apartmentName = "";
		String plancontent = "";
		if (!StringUtils.isBlank(buildingID)) {// 获取楼栋名称
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("buildingID", buildingID);
			buildingName = (String) this.getSqlSession().selectOne(
					"PropNoticeMapper.getbuildingName", map);
		}
		if (!StringUtils.isBlank(unitID)) {// 获取楼栋名称
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("unitID", unitID);
			unitName = (String) this.getSqlSession().selectOne(
					"PropNoticeMapper.getunitName", map);

			if (unitName == null) {

				unitName = "";

			}
		}
		if (apartmentID != null) {
			if (apartmentID.length > 0) {// 获取房号 存在多个
				Map<String, Object> map = new HashMap<String, Object>();
				map.put("apartmentID", apartmentID);
				apartmentName = "("
						+ (String) this.getSqlSession().selectOne(
								"PropNoticeMapper.getapartmentName", map) + ")";
			}
		}
		plancontent = buildingName + " " + unitName + " " + apartmentName + ","
				+ startTime + "~" + endTime + "," + limitDate;
		System.out.println("计划内容dd11========:" + plancontent);
		// //插入生成计划处理 prop_fee_proc_plan
		// Map<String, Object> planmap = new HashMap<String, Object>();
		// planmap.put("planID",planid);
		// planmap.put("commID", commID );
		// planmap.put("planName", plancontent);
		// planmap.put("planContent", plancontent);
		// planmap.put("planType", "2");
		// planmap.put("planStatus","2" );
		// //planmap.put("planLink", );
		// planmap.put("remark", remark);
		// // planmap.put("finishTime", );
		// // planmap.put("tscreate", );
		// // planmap.put("tsedit", );
		// planmap.put("itemTtat","1" );
		// this.getSqlSession().insert("PropNoticeMapper.insertpropfeeprocplan",
		// planmap);
		// 查询欠缴房屋信息
		Map<String, Object> fwxxmap = new HashMap<String, Object>();

		QueryModel q = new QueryModel();
		if (!StringUtils.isBlank(buildingID)) {// 获取楼栋名称
			fwxxmap.put("buildingID", buildingID);
			q.setBuildingID(buildingID);
		}
		if (!StringUtils.isBlank(unitID)) {// 获取楼栋名称
			fwxxmap.put("unitID", unitID);
			q.setUnitID(unitID);
		}
		fwxxmap.put("apartmentID", apartmentID);
		if (apartmentID.length > 0) {
			fwxxmap.put("apartmentIDstring", "11");
			q.setApartmentIDs(apartmentID);
		} else {
			fwxxmap.put("apartmentIDstring", null);
		}

		List<Map<String, Object>> fwxxlist = propInfoService
				.getApartmentInfo(q);
		System.out.println("ffffffffffff:" + fwxxlist);
		// 获取打印时间
		Map<String, Object> printtimemap = new HashMap<String, Object>();
		printtimemap.put("planid", planid);
		String printtime = (String) this.getSqlSession().selectOne(
				"PropNoticeMapper.getprinttime", printtimemap);
		// 生成打印计划excl

		PropFeeCommDto propFeeCommDto = propFeeCommService
				.findPropFeeCommByID(commID);
		// pmName = propFeeCommDto.getComm() + "物业服务收费通知单";
		String ljdz=null;
		//打印缴费通知单时需要用新的模板的pmID
        if(pmID.equalsIgnoreCase(ProConfig.getProValues("PROCPRINTPLANBYPMID"))){
			ljdz = outExcelNewByPmID(fwxxlist, planid, limitDate, printtime,
					commName, remark, pmName, startTime, endTime, commID,
					propFeeCommDto);

		}else{
			ljdz = outExcelNew(fwxxlist, planid, limitDate, printtime,
					commName, remark, pmName, startTime, endTime, commID,
					propFeeCommDto);
		}

		// 修改打印计划为完成
		Map<String, Object> wcmap = new HashMap<String, Object>();
		wcmap.put("planid", planid);
		wcmap.put("plan_status", "3");
		wcmap.put("ljdz", ljdz);

		wcmap.put("finishTime", new Date());
		this.getSqlSession().update("PropNoticeMapper.updatepropfeeprocplan",
				wcmap);
		// TODO Auto-generated method stubwcmap
		return planid;
	}

	public String outExcelNew(List<Map<String, Object>> list, String planid,
							  String limitDate, String printtime, String commName, String remark,
							  String pmName, String startTime, String endTime, String commID,
							  PropFeeCommDto propFeeCommDto) throws AppBizException,
			ParseException, Exception, IOException {

		String sfqj = "";
		if (startTime.equals(endTime)) {
			sfqj = startTime;
		} else {
			sfqj = startTime + "~" + endTime;
		}
		HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件
		HSSFSheet sheet = wb.createSheet("收费通知单"); // --->创建了一个工作簿

		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// anchor主要用于设置图片的属性

		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张

		System.out.println(printSetup.getFitHeight() + "***************"
				+ "&&&&&&");
		// sheet.setDisplayGridlines(false);
		// sheet.setPrintGridlines(false);System.out.println("vvvvvvvvvvvvvv:"+printSetup.getFitHeight());
		sheet.setMargin(HSSFSheet.TopMargin, (double) 0.3); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.3); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.7); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin, (double) 0.5); // 右边距

		HSSFDataFormat format = wb.createDataFormat(); // --->单元格内容格式//1200
		sheet.setColumnWidth((short) 0, 20 * 233); // ---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的
		sheet.setColumnWidth((short) 1, 20 * 233); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度
		sheet.setColumnWidth((short) 2, 20 * 350);
		sheet.setColumnWidth((short) 3, 20 * 217);
		sheet.setColumnWidth((short) 4, 20 * 230);
		sheet.setDefaultRowHeight((short) 300); // ---->有得时候你想设置统一单元格的高度，就用这个方法
		sheet.protectSheet("123456");
		int sheetrow = 0;
		int numrow = 0;
		/** 所有行的样式开始 **/
		// 样式1 第一行和第二行样式
		HSSFCellStyle style = wb.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style.setBorderLeft((short) 1); // 设置左边框
		style.setBorderRight((short) 1); // 设置有边框
		style.setBorderTop((short) 1); // 设置下边框

		Font font = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setItalic(true); // --->设置是否是加粗

		font.setFontHeightInPoints((short) 13); // --->设置字体大小
		style.setFont(font);

		HSSFCellStyle style2 = wb.createCellStyle(); // 样式对象
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style2.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style2.setBorderLeft((short) 1); // 设置左边框
		style2.setBorderRight((short) 1); // 设置有边框
		style2.setBorderTop((short) 1); // 设置下边框

		Font font2 = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font2.setItalic(true); // --->设置是否是加粗
		font2.setFontHeightInPoints((short) 12); // --->设置字体大小
		style2.setFont(font2);

		// 第三行样式
		HSSFCellStyle style3 = wb.createCellStyle(); // 样式对象
		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		// style3.setBorderBottom((short)0); //设置下划线，参数是黑线的宽度
		// style3.setBorderLeft((short)0); //设置左边框
		// style3.setBorderRight((short)1); //设置有边框
		// style3.setBorderTop((short)0); //设置下边框
		Font font3 = wb.createFont();
		font3.setFontHeightInPoints((short) 5); // --->设置字体大小
		style3.setFont(font3);

		HSSFCellStyle stylecel = wb.createCellStyle(); // 样式对象
		stylecel.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel.setBorderLeft((short) 1); // 设置左边框
		stylecel.setBorderRight((short) 0); // 设置有边框
		stylecel.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel1 = wb.createCellStyle(); // 样式对象
		stylecel1.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel1.setBorderLeft((short) 0); // 设置左边框
		stylecel1.setBorderRight((short) 1); // 设置有边框
		stylecel1.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel2 = wb.createCellStyle(); // 样式对象
		// stylecel2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		stylecel2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平
		stylecel2.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel2.setBorderLeft((short) 0); // 设置左边框
		stylecel2.setBorderRight((short) 1); // 设置有边框
		stylecel2.setBorderTop((short) 0); // 设置下边框

		// 样式1 第一行和第二行样式
		HSSFCellStyle style5 = wb.createCellStyle(); // 样式对象
		style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style5.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style5.setBorderLeft((short) 1); // 设置左边框
		style5.setBorderRight((short) 1); // 设置有边框
		style5.setBorderTop((short) 1); // 设置下边框
		// Font font5 = wb.createFont();
		// font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font5.setItalic(true); //--->设置斜体
		// font5.setFontHeightInPoints((short)8); //--->设置字体大小
		// font5.set
		// style5.setFont(font5);

		HSSFCellStyle style6 = wb.createCellStyle(); // 样式对象
		style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style6.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style6.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style6.setBorderLeft((short) 1); // 设置左边框
		style6.setBorderRight((short) 1); // 设置有边框
		style6.setBorderTop((short) 1); // 设置下边框
		style6.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第11行
		HSSFCellStyle style11 = wb.createCellStyle(); // 样式对象
		style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style11.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style11.setBorderLeft((short) 1); // 设置左边框
		style11.setBorderRight((short) 1); // 设置有边框
		style11.setBorderTop((short) 1); // 设置下边框
		Font font11 = wb.createFont();
		font11.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		font11.setFontHeightInPoints((short) 8); // --->设置字体大小
		style11.setFont(font11);

		// 表格第12行
		HSSFCellStyle style12 = wb.createCellStyle(); // 样式对象
		style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style12.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style12.setBorderLeft((short) 1); // 设置左边框
		style12.setBorderRight((short) 1); // 设置有边框
		style12.setBorderTop((short) 1); // 设置下边框
		style12.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第12行
		HSSFCellStyle style13 = wb.createCellStyle(); // 样式对象
		style13.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		// style13.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style13.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style13.setBorderLeft((short) 1); // 设置左边框
		style13.setBorderRight((short) 1); // 设置有边框
		style13.setBorderTop((short) 1); // 设置下边框
		style13.setWrapText(true); // 设置是否能够换行，能够换行为true

		HSSFCellStyle style14 = wb.createCellStyle(); // 样式对象
		style14.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);// 垂直
		// style13.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style14.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style14.setBorderLeft((short) 1); // 设置左边框
		style14.setBorderRight((short) 1); // 设置有边框
		style14.setBorderTop((short) 1); // 设置下边框
		style14.setWrapText(true); // 设置是否能够换行，能够换行为true
		/** 所有行的样式结束 **/
		// //设置标题字体格式
		// // Font font = wb.createFont();
		// Font font1 = wb.createFont();
		// //设置字体样式
		// // font.setFontHeightInPoints((short)20); //--->设置字体大小
		// // font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font1.setItalic(true); //--->设置是否是加粗
		// style1.setFont(font1); //--->将字体格式加入到style1中
		// //style1.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		// //style1.setFillPattern(CellStyle.SOLID_FOREGROUND);设置单元格颜色
		// style1.setWrapText(true); //设置是否能够换行，能够换行为true
		// style1.setBorderBottom((short)1); //设置下划线，参数是黑线的宽度
		// style1.setBorderLeft((short)1); //设置左边框
		// style1.setBorderRight((short)1); //设置有边框
		// style1.setBorderTop((short)1); //设置下边框
		// style4.setDataFormat(format.getFormat("￥#,##0")); //--->设置为单元格内容为货币格式
		//
		// style5.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
		// //--->设置单元格内容为百分数格式
		int counttzdgd = 0;// 整通知单高度

		int H = 0;
		int mh = 0;
		int pageH = 0;

		// 获取当前账单生成

		Map<String, String> lastDate = propFeeCommInfoService
				.findLastDate(commID);
		String thisTime = lastDate.get("lastTime");
		Map<String, Map<String, PreDetail>> pre = null;
		List<Map<String, Object>> fwqflist1 = null;
		// 比较打印结束时间和账单生成时间
		if (endTime.compareTo(thisTime) > 0) {

			BillDto billDto = new BillDto();
			billDto.setCommID(commID);

			billDto.setStartTime(DateUtil.addMonth(thisTime, 1));
			billDto.setEndTime(endTime);
			pre = computeBillService.commPreFeeDetail(billDto, list);
		}

		Map<String, Map<String, Object>> rules = new HashMap<>();
		Map<String, String> param = new HashMap<>();
		param.put("commID", commID);
		PropFeeAccountDto propFeeAccountDto = null;
		Map<String, Object> map = null;
		for (int i = 0; i < list.size(); i++) {
			map = list.get(i);
			String fwxx = (String) map.get("building_name")
					+ StringUtils.toString(map.get("unit_name"), "")
					+ (String) map.get("apartment_num");
			String fwjzmj = StringUtils.toString(map.get("fee_area"), "");
			String owners = StringUtils.toString(map.get("ownerName"), "");
			String ownerName = "";
			if (!StringUtils.isBlank(owners)) {
				String temp[] = owners.split(",");
				ownerName = temp[0];
			}
			// 获取该房屋的欠费信息
			Map<String, Object> fwqfmap = new HashMap<String, Object>();
			String apartmentID = map.get("apartment_id").toString();
			fwqfmap.put("apartment_id", map.get("apartment_id").toString());
			fwqfmap.put("startTime", startTime);
			fwqfmap.put("endTime", endTime);
			fwqfmap.put("commID", commID);

			propFeeAccountDto = propFeeAccountService
					.findAccountByApartID(apartmentID);

			param.put("apartmentID", apartmentID);

			param.put("feeArea", fwjzmj);

			// List<PropFeeAccountDto>propFeeAccountDtos=

			Map<String, Object> totalMap = this.getSqlSession().selectOne(
					"PropNoticeMapper.getfhqTotal", fwqfmap);
			BigDecimal totalAmount = new BigDecimal(totalMap.get("totalAmount")
					.toString());
			if (totalAmount.compareTo(BigDecimal.ZERO) <= 0) {

				if (pre == null
						|| pre.get(map.get("apartment_id").toString()) == null
						|| pre.get(map.get("apartment_id").toString()).size() == 0) {

					continue;

				}
			}

			// 处理缴费数据

			fwqflist1 = this.getSqlSession().selectList(
					"PropNoticeMapper.getfhqfmxtzd3", fwqfmap);

			List<Map<String, Object>> fwqflist = dealNoticeFeedetailNew2(
					fwqflist1, pre != null ? pre.get(apartmentID) : null,
					rules, param);

			System.out.println(fwqflist);

			// if(1==1)
			//
			// return null;

			// 获取预缴的费用

			System.out.println("获取该房屋的欠费信息==============:" + fwqflist);
			// String
			counttzdgd += 1900 + fwqflist.size() * 400;

			// if(counttzdgd>18500){
			// sheet.setAutobreaks(false);
			// sheet.setRowBreak(numrow);// 設置每30行分頁打印
			// counttzdgd=1900+fwqflist.size()*400;
			// }
			int tzdgd = 0;// 通知单高度
			// 表格第一行
			numrow = sheetrow++;
			HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 28, 1000, 236,
					(short) 0, numrow, (short) 0, numrow);
			anchor1.setAnchorType(3);
			HSSFRow row0 = sheet.createRow(numrow); // --->创建一行
			HSSFCell cell0 = row0.createCell(0);
			HSSFCell cell01 = row0.createCell(1);
			HSSFCell cell02 = row0.createCell(2);
			HSSFCell cell03 = row0.createCell(3);
			HSSFCell cell04 = row0.createCell(4);
			cell01.setCellStyle(style);
			cell02.setCellStyle(style);
			cell03.setCellStyle(style);
			cell04.setCellStyle(style);

			// 四个参数分别是：起始行，起始列，结束行，结束列
			sheet.addMergedRegion(new CellRangeAddress(numrow, (short) numrow,
					0, (short) 4));
			row0.setHeightInPoints(30);
			// row0.setRowStyle(style);
			tzdgd += row0.getHeight();
			// --->创建一个单元格
			cell0.setCellStyle(style);
			// row0.getCell(1).setCellStyle(style);

			cell0.setCellValue(pmName);
			cell0.setCellStyle(style2);

			// 表格第二行
			numrow = sheetrow++;
			HSSFRow row1 = sheet.createRow(numrow);

			HSSFCell cell1 = row1.createCell(0);
			HSSFCell cell1_1 = row1.createCell(1);
			HSSFCell cell1_2 = row1.createCell(2);
			HSSFCell cell1_3 = row1.createCell(3);
			HSSFCell cell1_4 = row1.createCell(4);

			cell1_1.setCellStyle(style);
			cell1_2.setCellStyle(style);
			cell1_3.setCellStyle(style);
			cell1_4.setCellStyle(style);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 4));
			row1.setHeightInPoints(20);
			// row1.setRowStyle(style);
			tzdgd += row1.getHeight();

			cell1.setCellValue("收费通知单");
			cell1.setCellStyle(style);

			// 表格第三行
			numrow = sheetrow++;
			HSSFRow row3 = sheet.createRow(numrow);

			row3.setRowStyle(style3);
			tzdgd += row3.getHeight();
			// row3.setHeightInPoints(20);
			HSSFCell cell3 = row3.createCell(0);
			cell3.setCellValue("收费期间：" + sfqj);
			cell3.setCellStyle(stylecel);

			HSSFCell cell3_2 = row3.createCell(1);
			cell3_2.setCellValue("");
			cell3_2.setCellStyle(stylecel1);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));

			HSSFCell cell3_3 = row3.createCell(2);
			cell3_3.setCellValue("打印时间：" + printtime);
			cell3_3.setCellStyle(stylecel1);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));
			HSSFCell cell3_4 = row3.createCell(3);
			cell3_4.setCellValue("小区名称：" + commName);
			cell3_4.setCellStyle(stylecel1);

			HSSFCell cell3_5 = row3.createCell(4);
			cell3_5.setCellValue("");
			cell3_5.setCellStyle(stylecel1);

			// 表格第四行
			numrow = sheetrow++;
			HSSFRow row4 = sheet.createRow(numrow);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));

			row4.setRowStyle(style3);
			// row4.setHeightInPoints(15);
			tzdgd += row4.getHeight();
			HSSFCell cell4 = row4.createCell(0);
			cell4.setCellValue("房屋信息：" + fwxx);
			cell4.setCellStyle(stylecel);

			HSSFCell cell4_2 = row4.createCell(1);
			cell4_2.setCellValue("");
			cell4_2.setCellStyle(stylecel1);

			// row4.setRowStyle(stylecel2);
			HSSFCell cell4_3 = row4.createCell(2);
			cell4_3.setCellValue("业主名称：" + ownerName);
			cell4_3.setCellStyle(stylecel1);
			HSSFCell cell4_5 = row4.createCell(3);
			cell4_5.setCellValue("房屋计费面积：" + fwjzmj + "㎡");
			cell4_5.setCellStyle(stylecel);
			HSSFCell cell4_6 = row4.createCell(4);
			cell4_6.setCellValue("");
			cell4_6.setCellStyle(stylecel2);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));

			// 表格第五行
			numrow = sheetrow++;
			HSSFRow row5 = sheet.createRow(numrow);

			tzdgd += row5.getHeight();
			// row5.setHeightInPoints(25);

			HSSFCell cell5_0 = row5.createCell(0);
			cell5_0.setCellValue("费用名称");
			cell5_0.setCellStyle(style5);

			HSSFCell cell5_1 = row5.createCell(1);
			cell5_1.setCellValue("计费用量");
			cell5_1.setCellStyle(style5);

			HSSFCell cell5_2 = row5.createCell(2);
			cell5_2.setCellValue("单价(元)");
			cell5_2.setCellStyle(style5);

			HSSFCell cell5_3 = row5.createCell(3);
			cell5_3.setCellValue("合计应缴金额(元)");
			cell5_3.setCellStyle(style5);

			HSSFCell cell5_4 = row5.createCell(4);
			cell5_4.setCellValue("");
			cell5_4.setCellStyle(style5);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));

			// 生成快照
			Map<String, Object> kzmap = new HashMap<String, Object>();
			kzmap.put("notice_id", IdUtil.getuuId());
			kzmap.put("plan_id", planid);
			kzmap.put("apartment_id", map.get("apartment_id").toString());
			kzmap.put("fee_range", sfqj);
			kzmap.put("limit_date", limitDate);
			kzmap.put("content", fwqflist.toString());
			// kzmap.put("tscreate", );
			// kzmap.put("tsedit", );
			kzmap.put("item_stat", "1");
			this.getSqlSession().insert(
					"PropNoticeMapper.insertpropfeebillnotice", kzmap);
			BigDecimal bigsum = new BigDecimal("0.00");// 总计

			// 公摊费用
			Map<String, String> gt = new HashMap<String, String>();
			gt.put("feeTypeName", "公摊费用");
			gt.put("feeAmount", "-");
			gt.put("feePrice", "-");
			gt.put("stardate", "");
			gt.put("enddate", "");

			BigDecimal temp = BigDecimal.ZERO;

			for (int j = 0; j < fwqflist.size(); j++) {
				Map<String, Object> qjxxmap = fwqflist.get(j);
				String feeTypeName = qjxxmap.get("feeTypeName").toString();

				String amount = StringUtils.toString(qjxxmap.get("amount"));

				// if(new bIG)
				String oldstardate = StringUtils.toString(qjxxmap
						.get("stardate"));
				String oldenddate = StringUtils
						.toString(qjxxmap.get("enddate"));
				// if (pre != null) {
				// temp = BigDecimal.ZERO;
				//
				// if (pre.get(map.get("apartment_id").toString()) != null) {
				// PreDetail temp1 = pre.get(
				// map.get("apartment_id").toString()).get(
				// qjxxmap.get("feeTypeID").toString());
				//
				// if (temp1 != null) {
				//
				// amount = new BigDecimal(StringUtils.toString(
				// amount, "0.00")).add(temp1.getAmount())
				// .toString();
				// qjxxmap.put("feeAmount", temp1.getFeeAmount());
				// qjxxmap.put("feePrice", temp1.getFeePrice());
				// if (oldstardate == null) {
				//
				// oldstardate = DateUtil.addMonth(thisTime, 1);
				//
				// }
				// oldenddate = endTime;
				// }
				//
				// }
				//
				// }
				String feeAmount = StringUtils.toString(
						qjxxmap.get("feeAmount"), "——");
				String feePrice = StringUtils.toString(qjxxmap.get("feePrice"),
						"——");

				String type = StringUtils.toString(qjxxmap.get("type"));

				// if ("1".equals(type)) {
				// if (!StringUtils.isBlank(oldstardate)) {
				//
				// gt_amount = gt_amount.add(new BigDecimal(amount));
				//
				// if (StringUtils.isBlank(gt.get("stardate"))) {
				// gt.put("stardate", oldstardate);
				// gt.put("enddate", oldenddate);
				//
				// } else if (oldstardate.compareTo(gt.get("stardate")) < 0) {
				//
				// gt.put("stardate", oldstardate);
				// } else {
				// if (oldenddate.compareTo(gt.get("enddate")) > 0) {
				//
				// gt.put("enddate", oldenddate);
				//
				// }
				//
				// }
				//
				// }
				//
				// continue;
				// }

				// String oldamount=qjxxmap.get("oldamount").toString();
				// String sumamount=qjxxmap.get("sumamount").toString();
				if (StringUtils.isBlank(amount)) {
					// bigsum=bigsum.add(new BigDecimal("0.0"));
				} else {
					bigsum = bigsum.add(new BigDecimal(amount));
				}

				if (!StringUtils.isBlank(oldstardate)) {

					amount = String.format("%10s", amount)
							+ "   "
							+ "("
							+ (oldstardate.equals(oldenddate) ? oldstardate
							: (oldstardate + "~" + oldenddate)) + ")";

					//
					// if (oldstardate.equals(oldenddate)) {
					// amount = amount+"0   " + "(" + oldstardate + ")";
					// } else {
					// amount = amount+"0   " + "(" + oldstardate + "~"
					// + oldenddate + ")";
					// }
				} else {

					amount = "——";

				}

				System.out.println("总计==========：" + bigsum);

				// 表格第六行
				numrow = sheetrow++;
				HSSFRow row6 = sheet.createRow(numrow);
				tzdgd += row6.getHeight();
				// row6.setHeightInPoints(25);
				HSSFCell cell6_0 = row6.createCell(0);
				cell6_0.setCellValue(feeTypeName);
				cell6_0.setCellStyle(style6);

				HSSFCell cell6_1 = row6.createCell(1);
				cell6_1.setCellValue(feeAmount);
				cell6_1.setCellStyle(style6);

				HSSFCell cell6_2 = row6.createCell(2);
				cell6_2.setCellValue(feePrice);
				cell6_2.setCellStyle(style6);

				HSSFCell cell6_3 = row6.createCell(3);
				cell6_3.setCellValue(amount);
				cell6_3.setCellStyle(style6);

				HSSFCell cell6_4 = row6.createCell(4);
				cell6_4.setCellValue(amount);
				cell6_4.setCellStyle(style6);
				sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
						(short) 3, (short) 4));

			}

			// numrow = sheetrow++;
			// HSSFRow row6 = sheet.createRow(numrow);
			// tzdgd += row6.getHeight();
			// // row6.setHeightInPoints(25);
			// HSSFCell cell6_0 = row6.createCell(0);
			// cell6_0.setCellValue("公摊费用");
			// cell6_0.setCellStyle(style6);
			//
			// HSSFCell cell6_1 = row6.createCell(1);
			// cell6_1.setCellValue("——");
			// cell6_1.setCellStyle(style6);
			//
			// HSSFCell cell6_2 = row6.createCell(2);
			// cell6_2.setCellValue("——");
			// cell6_2.setCellStyle(style6);
			//
			// HSSFCell cell6_3 = row6.createCell(3);
			//
			// String amount11 = "——";
			// if (!StringUtils.isBlank(gt.get("stardate"))) {
			// bigsum = bigsum.add(new BigDecimal(gt_amount.toString()));
			//
			// amount11 = String.format("%10s", gt_amount.toString()) + "   "
			// + "(" + gt.get("stardate") + "~" + gt.get("enddate")
			// + ")";
			//
			// }
			//
			// cell6_3.setCellValue(amount11);
			// cell6_3.setCellStyle(style6);

			// 第11行

			numrow = sheetrow++;
			HSSFRow row11 = sheet.createRow(numrow);
			row11.setHeightInPoints(13);
			tzdgd += row11.getHeight();
			// row11.setRowStyle(style11);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow + 2,
					(short) 0, (short) 1));
			HSSFCell cell11_11 = row11.createCell(1);
			cell11_11.setCellStyle(style11);
			HSSFCell cell11_0 = row11.createCell(0);
			cell11_0.setCellValue("缴费期限：" + limitDate);
			cell11_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell11_1 = row11.createCell(2);
			HSSFCell cell11_3 = row11.createCell(3);
			HSSFCell cell11_4 = row11.createCell(4);
			cell11_3.setCellStyle(style11);
			cell11_4.setCellStyle(style11);
			cell11_1.setCellValue("总计金额（小写）：" + bigsum.toString());
			cell11_1.setCellStyle(style11);

			numrow = sheetrow++;
			HSSFRow row13 = sheet.createRow(numrow);
			row13.setHeightInPoints(13);

			// row13.setRowStyle(style11);
			tzdgd += row13.getHeight();
			// sheet.addMergedRegion(new CellRangeAddress(numrow-1, numrow,
			// (short) 0, (short) 1));
			// // HSSFCell cell13_0 = row11.createCell(0);
			// // cell13_0.setCellValue("缴费期限："+limitDate);
			// // cell13_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell13_0 = row13.createCell(0);
			HSSFCell cell13_1 = row13.createCell(2);
			HSSFCell cell13_2 = row13.createCell(3);
			HSSFCell cell13_4 = row13.createCell(4);
			cell13_0.setCellStyle(style11);
			cell13_2.setCellStyle(style11);
			cell13_4.setCellStyle(style11);
			cell13_1.setCellValue("总计金额（大写）："
					+ cnUpperCaser.number2CNMontrayUnit(bigsum));
			cell13_1.setCellStyle(style11);

			numrow = sheetrow++;
			HSSFRow row14_1 = sheet.createRow(numrow);
			row13.setHeightInPoints(14);

			// row13.setRowStyle(style11);
			tzdgd += row14_1.getHeight();
			// sheet.addMergedRegion(new CellRangeAddress(numrow-1, numrow,
			// (short) 0, (short) 1));
			// // HSSFCell cell13_0 = row11.createCell(0);
			// // cell13_0.setCellValue("缴费期限："+limitDate);
			// // cell13_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell14_0 = row14_1.createCell(0);
			HSSFCell cell14_1 = row14_1.createCell(2);
			HSSFCell cell14_2 = row14_1.createCell(3);
			HSSFCell cell14_4 = row14_1.createCell(4);
			cell14_0.setCellStyle(style11);
			cell14_2.setCellStyle(style11);
			cell14_4.setCellStyle(style11);

			if (propFeeAccountDto != null) {
				cell14_1.setCellValue("房屋账户余额:"
						+ (propFeeAccountDto.getMoney() != null ? propFeeAccountDto
						.getMoney() + ""
						: "0.00"));
			} else {
				cell14_1.setCellValue("房屋账户余额: 0.00");
			}
			cell14_1.setCellStyle(style11);

			// 第12行
			numrow = sheetrow++;
			HSSFRow row12 = sheet.createRow(numrow);
			int nn = 2000;
			int n = 0;
			if (!StringUtils.isBlank(remark)) {

				n = remark.split("\n").length;
			}

			if (n > 6) {

				nn = n * 350;
			}

			// row12.setRowStyle(style12);
			row12.setHeight((short) nn);

			tzdgd += row12.getHeight();

			HSSFCell cell12_0 = row12.createCell(0);

			cell12_0.setCellValue("备注：");
			cell12_0.setCellStyle(style12);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 1, (short) 3));
			HSSFCell cell12_1 = row12.createCell(1);
			HSSFCell cell12_2 = row12.createCell(2);
			HSSFCell cell12_3 = row12.createCell(3);
			HSSFCell cell12_4 = row12.createCell(4);

			int col1width = (int) Math.round((sheet.getColumnWidth(4) / 45));
			int height = nn / 20;
			// gao
			int n1 = 1400 * 255 / 1800;

			int n2 = (255 - n1) / 2;

			int wid = 75 * 1023 / col1width;
			HSSFClientAnchor anchor = new HSSFClientAnchor(120, 20, 100 + wid,
					n1 - 10, (short) 4, numrow, (short) 4, numrow);
			anchor.setAnchorType(3);

			// ByteArrayOutputStream byteArrayOut =
			// MatrixToImageWriter.getMatrix(
			// 100, 100, value);

			// 插入图片

			ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();

			byteArrayOut1.write(ProConfig.readStream(ProConfig.getImage()));
			patriarch.createPicture(anchor1,
					wb.addPicture(byteArrayOut1.toByteArray(),
							HSSFWorkbook.PICTURE_TYPE_JPEG));

			String value = ProConfig.getProValues("paybillNoticeUrl")
					+ "?commID=" + commID + "&apartmentID="
					+ map.get("apartment_id").toString();
			// ByteArrayOutputStream byteArrayOut =
			// MatrixToImageWriter.getMatrix(
			// 100, 100, value);

			// 插入图片
			if (1 == 1) {

				if (!StringUtils.isBlank(propFeeCommDto.getQrcode())) {

					ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
					byteArrayOut
							.write(Base64.decode(propFeeCommDto.getQrcode()));
					patriarch.createPicture(anchor, wb.addPicture(
							byteArrayOut.toByteArray(),
							HSSFWorkbook.PICTURE_TYPE_JPEG));
				}

			}

			cell12_1.setCellValue(remark);
			cell12_1.setCellStyle(style13);
			cell12_1.setCellStyle(style13);
			cell12_2.setCellStyle(style13);
			cell12_3.setCellStyle(style13);
			cell12_3.setCellStyle(style13);

			cell12_4.setCellStyle(style14);

			String payRemark = ProConfig.getProValues("PAYREMARK");
			cell12_4.setCellValue(!StringUtils.isBlank(propFeeCommDto
					.getQrcode()) ? payRemark : "");

			// numrow = sheetrow++;
			// HSSFRow row14 = sheet.createRow(numrow);
			// // row13.setHeightInPoints(200);
			// tzdgd += row14.getHeight();
			System.out.println("通知单打印高度:" + tzdgd);

			if (H == 0) {

				H = tzdgd;

			}

			pageH += H;

			if ((pageH + H + 200) > 18000) {

				System.out.println(pageH + "********" + numrow + "换页");
				sheet.setAutobreaks(false);
				sheet.setRowBreak(numrow);

				pageH = 0;

			} else {

				System.out.println(pageH + "********" + numrow);
				numrow = sheetrow++;
				HSSFRow row14 = sheet.createRow(numrow);
				row14.setHeight((short) 200);
				System.out.println(pageH + "********" + row14.getHeight());

				pageH = pageH + 200;

			}

			// int pagesize = 18500 / tzdgd;
			// if (i % pagesize == 0 && i != 0) {
			//
			// sheet.setAutobreaks(false);
			// sheet.setRowBreak(numrow);// 設置每30行分頁打印
			// }

		}
		map = null;
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		wb.write(os);
		byte[] bytes = os.toByteArray();

		// 设置好账号的ACCESS_KEY和SECRET_KEY
		String ACCESS_KEY = ProConfig.getProValues("ACCESS_KEY");
		String SECRET_KEY = ProConfig.getProValues("SECRET_KEY");
		// 要上传的空间
		String bucketname = ProConfig.getProValues("bucketname");
		// 上传到七牛后保存的文件名
		String key = DateUtil.DateToString(new Date(), "yyyyMMddHHmmss")
				+ ".xls";
		// 上传文件的路径
		// 密钥配置
		Auth auth = Auth.create(ACCESS_KEY, SECRET_KEY);
		// 创建上传对象
		UploadManager uploadManager = new UploadManager();

		// 调用put方法上传
		// Response res = uploadManager.put(FilePath, null,
		// auth.uploadToken(bucketname));
		Response res = uploadManager.put(bytes, key,
				auth.uploadToken(bucketname));

		// http://7xir0t.com2.z0.glb.qiniucdn.com/9c7de7e1-3f47-4970-9c4e-470120ee2e7a.xls

		String ljdz = ProConfig.getProValues("QINIU_URL") + key;

		// FileOutputStream fileOut = null;
		// try{
		//
		// fileOut = new FileOutputStream("d:\\workbook.xls");
		// wb.write(fileOut);
		// //fileOut.close();
		// System.out.print("OK");
		// }catch(Exception e){
		// e.printStackTrace();
		// }
		// finally{
		// if(fileOut != null){
		// try {
		// fileOut.close();
		// } catch (IOException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }
		// }
		// }

		return ljdz;
	}
	public String outExcelNewByPmID(List<Map<String, Object>> list, String planid,
			String limitDate, String printtime, String commName, String remark,
			String pmName, String startTime, String endTime, String commID,
			PropFeeCommDto propFeeCommDto) throws AppBizException,
			ParseException, Exception, IOException {

		String sfqj = "";
		if (startTime.equals(endTime)) {
			sfqj = startTime;
		} else {
			sfqj = startTime + "~" + endTime;
		}
		HSSFWorkbook wb = new HSSFWorkbook(); // --->创建了一个excel文件
		HSSFSheet sheet = wb.createSheet("收费通知单"); // --->创建了一个工作簿

		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// anchor主要用于设置图片的属性

		HSSFPrintSetup printSetup = sheet.getPrintSetup();
		printSetup.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); // 纸张

		System.out.println(printSetup.getFitHeight() + "***************"
				+ "&&&&&&");
		// sheet.setDisplayGridlines(false);
		// sheet.setPrintGridlines(false);System.out.println("vvvvvvvvvvvvvv:"+printSetup.getFitHeight());
		sheet.setMargin(HSSFSheet.TopMargin, (double) 0.3); // 上边距
		sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.3); // 下边距
		sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.7); // 左边距
		sheet.setMargin(HSSFSheet.RightMargin, (double) 0.5); // 右边距

		HSSFDataFormat format = wb.createDataFormat(); // --->单元格内容格式//1200
		sheet.setColumnWidth((short) 0, 20 * 233); // ---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的
		sheet.setColumnWidth((short) 1, 20 * 233); // --->第一个参数是指哪个单元格，第二个参数是单元格的宽度
		sheet.setColumnWidth((short) 2, 20 * 350);
		sheet.setColumnWidth((short) 3, 20 * 217);
		sheet.setColumnWidth((short) 4, 20 * 230);
		sheet.setDefaultRowHeight((short) 300); // ---->有得时候你想设置统一单元格的高度，就用这个方法
		sheet.protectSheet("123456");
		int sheetrow = 0;
		int numrow = 0;
		/** 所有行的样式开始 **/
		// 样式1 第一行和第二行样式
		HSSFCellStyle style = wb.createCellStyle(); // 样式对象
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style.setBorderLeft((short) 1); // 设置左边框
		style.setBorderRight((short) 1); // 设置有边框
		style.setBorderTop((short) 1); // 设置下边框

		Font font = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font.setItalic(true); // --->设置是否是加粗

		font.setFontHeightInPoints((short) 13); // --->设置字体大小
		style.setFont(font);

		HSSFCellStyle style2 = wb.createCellStyle(); // 样式对象
		style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style2.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style2.setBorderLeft((short) 1); // 设置左边框
		style2.setBorderRight((short) 1); // 设置有边框
		style2.setBorderTop((short) 1); // 设置下边框

		Font font2 = wb.createFont();
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		// font2.setItalic(true); // --->设置是否是加粗
		font2.setFontHeightInPoints((short) 12); // --->设置字体大小
		style2.setFont(font2);

		// 第三行样式
		HSSFCellStyle style3 = wb.createCellStyle(); // 样式对象
		style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		// style3.setBorderBottom((short)0); //设置下划线，参数是黑线的宽度
		// style3.setBorderLeft((short)0); //设置左边框
		// style3.setBorderRight((short)1); //设置有边框
		// style3.setBorderTop((short)0); //设置下边框
		Font font3 = wb.createFont();
		font3.setFontHeightInPoints((short) 5); // --->设置字体大小
		style3.setFont(font3);

		HSSFCellStyle stylecel = wb.createCellStyle(); // 样式对象
		stylecel.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel.setBorderLeft((short) 1); // 设置左边框
		stylecel.setBorderRight((short) 0); // 设置有边框
		stylecel.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel1 = wb.createCellStyle(); // 样式对象
		stylecel1.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel1.setBorderLeft((short) 0); // 设置左边框
		stylecel1.setBorderRight((short) 1); // 设置有边框
		stylecel1.setBorderTop((short) 0); // 设置下边框

		HSSFCellStyle stylecel2 = wb.createCellStyle(); // 样式对象
		// stylecel2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		stylecel2.setAlignment(HSSFCellStyle.ALIGN_RIGHT);// 水平
		stylecel2.setBorderBottom((short) 0); // 设置下划线，参数是黑线的宽度
		stylecel2.setBorderLeft((short) 0); // 设置左边框
		stylecel2.setBorderRight((short) 1); // 设置有边框
		stylecel2.setBorderTop((short) 0); // 设置下边框

		// 样式1 第一行和第二行样式
		HSSFCellStyle style5 = wb.createCellStyle(); // 样式对象
		style5.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style5.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style5.setBorderLeft((short) 1); // 设置左边框
		style5.setBorderRight((short) 1); // 设置有边框
		style5.setBorderTop((short) 1); // 设置下边框
		// Font font5 = wb.createFont();
		// font5.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
		// font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font5.setItalic(true); //--->设置斜体
		// font5.setFontHeightInPoints((short)8); //--->设置字体大小
		// font5.set
		// style5.setFont(font5);

		HSSFCellStyle style6 = wb.createCellStyle(); // 样式对象
		style6.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style6.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style6.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style6.setBorderLeft((short) 1); // 设置左边框
		style6.setBorderRight((short) 1); // 设置有边框
		style6.setBorderTop((short) 1); // 设置下边框
		style6.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第11行
		HSSFCellStyle style11 = wb.createCellStyle(); // 样式对象
		style11.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style11.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style11.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style11.setBorderLeft((short) 1); // 设置左边框
		style11.setBorderRight((short) 1); // 设置有边框
		style11.setBorderTop((short) 1); // 设置下边框
		Font font11 = wb.createFont();
		font11.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
		font11.setFontHeightInPoints((short) 8); // --->设置字体大小
		style11.setFont(font11);

		// 表格第12行
		HSSFCellStyle style12 = wb.createCellStyle(); // 样式对象
		style12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		style12.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style12.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style12.setBorderLeft((short) 1); // 设置左边框
		style12.setBorderRight((short) 1); // 设置有边框
		style12.setBorderTop((short) 1); // 设置下边框
		style12.setWrapText(true); // 设置是否能够换行，能够换行为true

		// 表格第12行
		HSSFCellStyle style13 = wb.createCellStyle(); // 样式对象
		style13.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
		// style13.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style13.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style13.setBorderLeft((short) 1); // 设置左边框
		style13.setBorderRight((short) 1); // 设置有边框
		style13.setBorderTop((short) 1); // 设置下边框
		style13.setWrapText(true); // 设置是否能够换行，能够换行为true

		HSSFCellStyle style14 = wb.createCellStyle(); // 样式对象
		style14.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);// 垂直
		// style13.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
		style14.setBorderBottom((short) 1); // 设置下划线，参数是黑线的宽度
		style14.setBorderLeft((short) 1); // 设置左边框
		style14.setBorderRight((short) 1); // 设置有边框
		style14.setBorderTop((short) 1); // 设置下边框
		style14.setWrapText(true); // 设置是否能够换行，能够换行为true
		/** 所有行的样式结束 **/
		// //设置标题字体格式
		// // Font font = wb.createFont();
		// Font font1 = wb.createFont();
		// //设置字体样式
		// // font.setFontHeightInPoints((short)20); //--->设置字体大小
		// // font.setFontName("Courier New"); //---》设置字体，是什么类型例如：宋体
		// font1.setItalic(true); //--->设置是否是加粗
		// style1.setFont(font1); //--->将字体格式加入到style1中
		// //style1.setFillForegroundColor(IndexedColors.DARK_YELLOW.getIndex());
		// //style1.setFillPattern(CellStyle.SOLID_FOREGROUND);设置单元格颜色
		// style1.setWrapText(true); //设置是否能够换行，能够换行为true
		// style1.setBorderBottom((short)1); //设置下划线，参数是黑线的宽度
		// style1.setBorderLeft((short)1); //设置左边框
		// style1.setBorderRight((short)1); //设置有边框
		// style1.setBorderTop((short)1); //设置下边框
		// style4.setDataFormat(format.getFormat("￥#,##0")); //--->设置为单元格内容为货币格式
		//
		// style5.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
		// //--->设置单元格内容为百分数格式
		int counttzdgd = 0;// 整通知单高度

		int H = 0;
		int mh = 0;
		int pageH = 0;

		// 获取当前账单生成

		Map<String, String> lastDate = propFeeCommInfoService
				.findLastDate(commID);
		String thisTime = lastDate.get("lastTime");
		Map<String, Map<String, PreDetail>> pre = null;
		List<Map<String, Object>> fwqflist1 = null;
		// 比较打印结束时间和账单生成时间
		if (endTime.compareTo(thisTime) > 0) {

			BillDto billDto = new BillDto();
			billDto.setCommID(commID);

			billDto.setStartTime(DateUtil.addMonth(thisTime, 1));
			billDto.setEndTime(endTime);
			pre = computeBillService.commPreFeeDetail(billDto, list);
		}

		Map<String, Map<String, Object>> rules = new HashMap<>();
		Map<String, String> param = new HashMap<>();
		param.put("commID", commID);
		PropFeeAccountDto propFeeAccountDto = null;
		Map<String, Object> map = null;
		for (int i = 0; i < list.size(); i++) {
			map = list.get(i);
			String fwxx = (String) map.get("building_name")
					+ StringUtils.toString(map.get("unit_name"), "")
					+ (String) map.get("apartment_num");
			String fwjzmj = StringUtils.toString(map.get("fee_area"), "");
			String owners = StringUtils.toString(map.get("ownerName"), "");
			String ownerName = "";
			if (!StringUtils.isBlank(owners)) {
				String temp[] = owners.split(",");
				ownerName = temp[0];
			}
			// 获取该房屋的欠费信息
			Map<String, Object> fwqfmap = new HashMap<String, Object>();
			String apartmentID = map.get("apartment_id").toString();
			fwqfmap.put("apartment_id", map.get("apartment_id").toString());
			fwqfmap.put("startTime", startTime);
			fwqfmap.put("endTime", endTime);
			fwqfmap.put("commID", commID);

			propFeeAccountDto = propFeeAccountService
					.findAccountByApartID(apartmentID);

			param.put("apartmentID", apartmentID);

			param.put("feeArea", fwjzmj);

			// List<PropFeeAccountDto>propFeeAccountDtos=

			Map<String, Object> totalMap = this.getSqlSession().selectOne(
					"PropNoticeMapper.getfhqTotal", fwqfmap);
			BigDecimal totalAmount = new BigDecimal(totalMap.get("totalAmount")
					.toString());
			if (totalAmount.compareTo(BigDecimal.ZERO) <= 0) {

				if (pre == null
						|| pre.get(map.get("apartment_id").toString()) == null
						|| pre.get(map.get("apartment_id").toString()).size() == 0) {

					continue;

				}
			}

			// 处理缴费数据

			fwqflist1 = this.getSqlSession().selectList(
					"PropNoticeMapper.getfhqfmxtzd3", fwqfmap);

			List<Map<String, Object>> fwqflist = dealNoticeFeedetailNew2(
					fwqflist1, pre != null ? pre.get(apartmentID) : null,
					rules, param);

			System.out.println(fwqflist);

			// if(1==1)
			//
			// return null;

			// 获取预缴的费用

			System.out.println("获取该房屋的欠费信息==============:" + fwqflist);
			// String
			counttzdgd += 1900 + fwqflist.size() * 400;

			// if(counttzdgd>18500){
			// sheet.setAutobreaks(false);
			// sheet.setRowBreak(numrow);// 設置每30行分頁打印
			// counttzdgd=1900+fwqflist.size()*400;
			// }
			int tzdgd = 0;// 通知单高度
			// 表格第一行
			numrow = sheetrow++;
			HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 28, 1000, 236,
					(short) 0, numrow, (short) 0, numrow);
			anchor1.setAnchorType(3);
			HSSFRow row0 = sheet.createRow(numrow); // --->创建一行
			HSSFCell cell0 = row0.createCell(0);
			HSSFCell cell01 = row0.createCell(1);
			HSSFCell cell02 = row0.createCell(2);
			HSSFCell cell03 = row0.createCell(3);
			HSSFCell cell04 = row0.createCell(4);
			cell01.setCellStyle(style);
			cell02.setCellStyle(style);
			cell03.setCellStyle(style);
			cell04.setCellStyle(style);

			// 四个参数分别是：起始行，起始列，结束行，结束列
			sheet.addMergedRegion(new CellRangeAddress(numrow, (short) numrow,
					0, (short) 4));
			row0.setHeightInPoints(30);
			// row0.setRowStyle(style);
			tzdgd += row0.getHeight();
			// --->创建一个单元格
			cell0.setCellStyle(style);
			// row0.getCell(1).setCellStyle(style);

			cell0.setCellValue(pmName);
			cell0.setCellStyle(style2);

			// 表格第二行
			numrow = sheetrow++;
			HSSFRow row1 = sheet.createRow(numrow);

			HSSFCell cell1 = row1.createCell(0);
			HSSFCell cell1_1 = row1.createCell(1);
			HSSFCell cell1_2 = row1.createCell(2);
			HSSFCell cell1_3 = row1.createCell(3);
			HSSFCell cell1_4 = row1.createCell(4);

			cell1_1.setCellStyle(style);
			cell1_2.setCellStyle(style);
			cell1_3.setCellStyle(style);
			cell1_4.setCellStyle(style);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 4));
			row1.setHeightInPoints(20);
			// row1.setRowStyle(style);
			tzdgd += row1.getHeight();

			cell1.setCellValue("收费通知单");
			cell1.setCellStyle(style);

			// 表格第三行
			numrow = sheetrow++;
			HSSFRow row3 = sheet.createRow(numrow);

			row3.setRowStyle(style3);
			tzdgd += row3.getHeight();
			// row3.setHeightInPoints(20);
			HSSFCell cell3 = row3.createCell(0);
			cell3.setCellValue("收费期间：" + sfqj);
			cell3.setCellStyle(stylecel);

			HSSFCell cell3_2 = row3.createCell(1);
			cell3_2.setCellValue("");
			cell3_2.setCellStyle(stylecel1);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));

			HSSFCell cell3_3 = row3.createCell(2);
			cell3_3.setCellValue("打印时间：" + printtime);
			cell3_3.setCellStyle(stylecel1);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));
			HSSFCell cell3_4 = row3.createCell(3);
			cell3_4.setCellValue("小区名称：" + commName);
			cell3_4.setCellStyle(stylecel1);

			HSSFCell cell3_5 = row3.createCell(4);
			cell3_5.setCellValue("");
			cell3_5.setCellStyle(stylecel1);

			// 表格第四行
			numrow = sheetrow++;
			HSSFRow row4 = sheet.createRow(numrow);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));

			row4.setRowStyle(style3);
			// row4.setHeightInPoints(15);
			tzdgd += row4.getHeight();
			HSSFCell cell4 = row4.createCell(0);
			cell4.setCellValue("房屋信息：" + fwxx);
			cell4.setCellStyle(stylecel);

			HSSFCell cell4_2 = row4.createCell(1);
			cell4_2.setCellValue("");
			cell4_2.setCellStyle(stylecel1);

			// row4.setRowStyle(stylecel2);
			HSSFCell cell4_3 = row4.createCell(2);
			cell4_3.setCellValue("业主名称：" + ownerName);
			cell4_3.setCellStyle(stylecel1);
			HSSFCell cell4_5 = row4.createCell(3);
			cell4_5.setCellValue("房屋计费面积：" + fwjzmj + "㎡");
			cell4_5.setCellStyle(stylecel);
			HSSFCell cell4_6 = row4.createCell(4);
			cell4_6.setCellValue("");
			cell4_6.setCellStyle(stylecel2);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 3, (short) 4));



			// 生成快照
			Map<String, Object> kzmap = new HashMap<String, Object>();
			kzmap.put("notice_id", IdUtil.getuuId());
			kzmap.put("plan_id", planid);
			kzmap.put("apartment_id", map.get("apartment_id").toString());
			kzmap.put("fee_range", sfqj);
			kzmap.put("limit_date", limitDate);
			kzmap.put("content", fwqflist.toString());
			// kzmap.put("tscreate", );
			// kzmap.put("tsedit", );
			kzmap.put("item_stat", "1");
			this.getSqlSession().insert(
					"PropNoticeMapper.insertpropfeebillnotice", kzmap);
			BigDecimal bigsum = new BigDecimal("0.00");// 总计

			// 公摊费用
			Map<String, String> gt = new HashMap<String, String>();
			gt.put("feeTypeName", "公摊费用");
			gt.put("feeAmount", "-");
			gt.put("feePrice", "-");
			gt.put("stardate", "");
			gt.put("enddate", "");

			BigDecimal temp = BigDecimal.ZERO;
			for (int j = 0; j < fwqflist.size(); j++) {
				Map<String, Object> qjxxmap = fwqflist.get(j);
				String feeTypeName = qjxxmap.get("feeTypeName").toString();

				String amount = StringUtils.toString(qjxxmap.get("amount"));

				String oldstardate = StringUtils.toString(qjxxmap
						.get("stardate"));
				String oldenddate = StringUtils
						.toString(qjxxmap.get("enddate"));
				String feeAmount = StringUtils.toString(
						qjxxmap.get("feeAmount"), "——");
				String feePrice = StringUtils.toString(qjxxmap.get("feePrice"),
						"——");
				String type = StringUtils.toString(qjxxmap.get("type"));

				if (StringUtils.isBlank(amount)) {

				} else {
					bigsum = bigsum.add(new BigDecimal(amount));
				}

				if (!StringUtils.isBlank(oldstardate)) {

					amount = String.format("%10s", amount)
							+ "   "
							+ "("
							+ (oldstardate.equals(oldenddate) ? oldstardate
									: (oldstardate + "~" + oldenddate)) + ")";
				} else {

					amount = "——";

				}

				System.out.println("总计==========：" + bigsum);

			}
// 第5行


			numrow = sheetrow++;
			HSSFRow row5= sheet.createRow(numrow);
			row5.setHeightInPoints(13);
			tzdgd += row5.getHeight();
			// row11.setRowStyle(style11);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow + 1,
					(short) 0, (short) 1));
			HSSFCell cell5_11 = row5.createCell(1);
			cell5_11.setCellStyle(style11);
			HSSFCell cell5_0 = row5.createCell(0);
			cell5_0.setCellValue("截止至" + sfqj+",您需要交纳的金额");
			cell5_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell5_1 = row5.createCell(2);
			HSSFCell cell5_3 = row5.createCell(3);
			HSSFCell cell5_4 = row5.createCell(4);
			cell5_3.setCellStyle(style11);
			cell5_4.setCellStyle(style11);
			cell5_1.setCellValue("总计金额（小写）：" + bigsum.toString());
			cell5_1.setCellStyle(style11);

			numrow = sheetrow++;
			HSSFRow row6 = sheet.createRow(numrow);
			row6.setHeightInPoints(13);

			tzdgd += row6.getHeight();


			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell6_0 = row6.createCell(0);
			HSSFCell cell6_1 = row6.createCell(2);
			HSSFCell cell6_2 = row6.createCell(3);
			HSSFCell cell6_4 = row6.createCell(4);
			cell6_0.setCellStyle(style11);
			cell6_2.setCellStyle(style11);
			cell6_4.setCellStyle(style11);
			cell6_1.setCellValue("总计金额（大写）："
					+ cnUpperCaser.number2CNMontrayUnit(bigsum));
			cell6_1.setCellStyle(style11);


//			numrow = sheetrow++;
//			HSSFRow row7_1 = sheet.createRow(numrow);
//			row6.setHeightInPoints(14);

//			tzdgd += row7_1.getHeight();
//
//			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
//					(short) 2, (short) 4));
//			HSSFCell cell7_0 = row7_1.createCell(0);
//			HSSFCell cell7_1 = row7_1.createCell(2);
//			HSSFCell cell7_2 = row7_1.createCell(3);
//			HSSFCell cell7_4 = row7_1.createCell(4);
//			cell7_0.setCellStyle(style11);
//			cell7_2.setCellStyle(style11);
//			cell7_4.setCellStyle(style11);
//
//			if (propFeeAccountDto != null) {
//				cell7_1.setCellValue("房屋账户余额:"
//						+ (propFeeAccountDto.getMoney() != null ? propFeeAccountDto
//						.getMoney() + ""
//						: "0.00"));
//			} else {
//				cell7_1.setCellValue("房屋账户余额: 0.00");
//			}
//			cell7_1.setCellStyle(style11);





			// 第11行

			numrow = sheetrow++;
			HSSFRow row11 = sheet.createRow(numrow);
			row11.setHeightInPoints(13);
			tzdgd += row11.getHeight();
			// row11.setRowStyle(style11);
			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 0, (short) 1));
			HSSFCell cell11_11 = row11.createCell(1);
			cell11_11.setCellStyle(style11);
			HSSFCell cell11_0 = row11.createCell(0);
			cell11_0.setCellValue("缴费期限：");
			cell11_0.setCellStyle(style11);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 2, (short) 4));
			HSSFCell cell11_1 = row11.createCell(2);
			HSSFCell cell11_3 = row11.createCell(3);
			HSSFCell cell11_4 = row11.createCell(4);
			cell11_3.setCellStyle(style11);
			cell11_4.setCellStyle(style11);
			cell11_1.setCellValue(limitDate);
			cell11_1.setCellStyle(style11);



			// 第12行
			numrow = sheetrow++;
			HSSFRow row12 = sheet.createRow(numrow);
			int nn = 2000;
			int n = 0;
			if (!StringUtils.isBlank(remark)) {

				n = remark.split("\n").length;
			}

			if (n > 6) {

				nn = n * 350;
			}

			// row12.setRowStyle(style12);
			row12.setHeight((short) nn);

			tzdgd += row12.getHeight();

			HSSFCell cell12_0 = row12.createCell(0);

			cell12_0.setCellValue("备注：");
			cell12_0.setCellStyle(style12);

			sheet.addMergedRegion(new CellRangeAddress(numrow, numrow,
					(short) 1, (short) 3));
			HSSFCell cell12_1 = row12.createCell(1);
			HSSFCell cell12_2 = row12.createCell(2);
			HSSFCell cell12_3 = row12.createCell(3);
			HSSFCell cell12_4 = row12.createCell(4);

			int col1width = (int) Math.round((sheet.getColumnWidth(4) / 45));
			int height = nn / 20;
			// gao
			int n1 = 1400 * 255 / 1800;

			int n2 = (255 - n1) / 2;

			int wid = 75 * 1023 / col1width;
			HSSFClientAnchor anchor = new HSSFClientAnchor(120, 20, 100 + wid,
					n1 - 10, (short) 4, numrow, (short) 4, numrow);
			anchor.setAnchorType(3);

			// ByteArrayOutputStream byteArrayOut =
			// MatrixToImageWriter.getMatrix(
			// 100, 100, value);

			// 插入图片

			ByteArrayOutputStream byteArrayOut1 = new ByteArrayOutputStream();

			byteArrayOut1.write(ProConfig.readStream(ProConfig.getImage()));
			patriarch.createPicture(anchor1,
					wb.addPicture(byteArrayOut1.toByteArray(),
							HSSFWorkbook.PICTURE_TYPE_JPEG));

			String value = ProConfig.getProValues("paybillNoticeUrl")
					+ "?commID=" + commID + "&apartmentID="
					+ map.get("apartment_id").toString();
			// ByteArrayOutputStream byteArrayOut =
			// MatrixToImageWriter.getMatrix(
			// 100, 100, value);

			// 插入图片
			if (1 == 1) {

				if (!StringUtils.isBlank(propFeeCommDto.getQrcode())) {

					ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
					byteArrayOut
							.write(Base64.decode(propFeeCommDto.getQrcode()));
					patriarch.createPicture(anchor, wb.addPicture(
							byteArrayOut.toByteArray(),
							HSSFWorkbook.PICTURE_TYPE_JPEG));
				}

			}

			cell12_1.setCellValue(remark);
			cell12_1.setCellStyle(style13);
			cell12_1.setCellStyle(style13);
			cell12_2.setCellStyle(style13);
			cell12_3.setCellStyle(style13);
			cell12_3.setCellStyle(style13);

			cell12_4.setCellStyle(style14);

			String payRemark = ProConfig.getProValues("PAYREMARK");
			cell12_4.setCellValue(!StringUtils.isBlank(propFeeCommDto
					.getQrcode()) ? payRemark : "");

			// numrow = sheetrow++;
			// HSSFRow row14 = sheet.createRow(numrow);
			// // row13.setHeightInPoints(200);
			// tzdgd += row14.getHeight();
			System.out.println("通知单打印高度:" + tzdgd);

			if (H == 0) {

				H = tzdgd;

			}

			pageH += H;

			if ((pageH + H + 200) > 18000) {

				System.out.println(pageH + "********" + numrow + "换页");
				sheet.setAutobreaks(false);
				sheet.setRowBreak(numrow);

				pageH = 0;

			} else {

				System.out.println(pageH + "********" + numrow);
				numrow = sheetrow++;
				HSSFRow row14 = sheet.createRow(numrow);
				row14.setHeight((short) 200);
				System.out.println(pageH + "********" + row14.getHeight());

				pageH = pageH + 200;

			}

			// int pagesize = 18500 / tzdgd;
			// if (i % pagesize == 0 && i != 0) {
			//
			// sheet.setAutobreaks(false);
			// sheet.setRowBreak(numrow);// 設置每30行分頁打印
			// }

		}
		map = null;
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		wb.write(os);
		byte[] bytes = os.toByteArray();

		// 设置好账号的ACCESS_KEY和SECRET_KEY
		String ACCESS_KEY = ProConfig.getProValues("ACCESS_KEY");
		String SECRET_KEY = ProConfig.getProValues("SECRET_KEY");
		// 要上传的空间
		String bucketname = ProConfig.getProValues("bucketname");
		// 上传到七牛后保存的文件名
		String key = DateUtil.DateToString(new Date(), "yyyyMMddHHmmss")
				+ ".xls";
		// 上传文件的路径
		// 密钥配置
		Auth auth = Auth.create(ACCESS_KEY, SECRET_KEY);
		// 创建上传对象
		UploadManager uploadManager = new UploadManager();

		// 调用put方法上传
		// Response res = uploadManager.put(FilePath, null,
		// auth.uploadToken(bucketname));
		Response res = uploadManager.put(bytes, key,
				auth.uploadToken(bucketname));

		// http://7xir0t.com2.z0.glb.qiniucdn.com/9c7de7e1-3f47-4970-9c4e-470120ee2e7a.xls

		String ljdz = ProConfig.getProValues("QINIU_URL") + key;

		// FileOutputStream fileOut = null;
		// try{
		//
		// fileOut = new FileOutputStream("d:\\workbook.xls");
		// wb.write(fileOut);
		// //fileOut.close();
		// System.out.print("OK");
		// }catch(Exception e){
		// e.printStackTrace();
		// }
		// finally{
		// if(fileOut != null){
		// try {
		// fileOut.close();
		// } catch (IOException e) {
		// // TODO Auto-generated catch block
		// e.printStackTrace();
		// }
		// }
		// }

		return ljdz;
	}
}
